/** <a href="http://www.cpupk.com/decompiler">Eclipse Class Decompiler</a> plugin, Copyright (c) 2017 Chen Chao. **/
package com.minierp.dao.hibernate;

import com.minierp.bean.UserBean;
import com.minierp.dao.BusinessBillDao;
import com.minierp.dao.hibernate.BaseDaoImpl;
import com.minierp.helper.EncCommonHelper;
import com.minierp.helper.PageHelper;
import com.minierp.model.BusinessBill;
import com.minierp.model.BusinessProfit;
import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

public class BusinessBillDaoImpl extends BaseDaoImpl implements BusinessBillDao {
	private String getBlearSearchHql(String searchKey) {
		String str = "";
		if (searchKey != null) {
			searchKey = searchKey.trim();
		}

		if (searchKey != null && !searchKey.equals("")) {
			str = " and ((b.billCode like :billCode)  or (e.estName like :estName) or (e.nameSpell like :nameSpell) or (b.content like :content))";
		}

		return str;
	}

	private void setBlearSearchValue(Query q, String searchKey) {
		if (searchKey != null) {
			searchKey = searchKey.trim();
		}

		if (searchKey != null && !searchKey.equals("")) {
			q.setString("billCode", "%" + searchKey + "%");
			q.setString("estName", "%" + searchKey + "%");
			q.setString("nameSpell", "%" + searchKey + "%");
			q.setString("content", "%" + searchKey + "%");
		}

	}

	public List<?> getListForAccessory(String searchKey, long estManagerId,
			long tsId, UserBean ub, Date allowStart, String busiUplo,
			int startIndex, int num) throws Exception {
		String str = "";
		if (searchKey != null) {
			searchKey = searchKey.trim();
		}

		if (searchKey != null && !searchKey.equals("")) {
			str = " and ((b.bill_code like :billCode)  or (e.est_name like :estName) or (e.name_spell like :nameSpell) or (b.content like :content) or (b.designer_name_str like :designerName))";
		}

		if (tsId != 0L) {
			str = str + " and b.tally_set_id=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tally_set_id",
							ub, true);
		}

		if (estManagerId != 0L) {
			str = str + " and e.manager_id=" + estManagerId;
		}

		if (allowStart != null) {
			str = str + " and b.order_date>=:allowStart";
		}

		if (busiUplo.equals("UNMAK")) {
			str = str + " and be.examined=\'Y\' and ie.maked=\'N\'";
		} else {
			str = str + " and be.examined=\'N\'";
		}

		String sql = "select bb.id,bb.bill_code,bb.order_date,a.est_name,bb.linkman,bb.content,bb.deliver_date,bb.designer_name_str,bb.design_type,a.wc_id from (select i.wc_id,b.id,e.est_name  from business_bill_item i left join business_bill_item_ext ie on i.id=ie.id left join business_bill b on b.id=i.bill_id left join business_bill_ext be on b.id=be.id left join establishment e on e.id=b.client_id where be.enabled=\'Y\' and be.closed=\'N\'"
				+ str
				+ " group by i.wc_id,b.id,e.est_name"
				+ ") a"
				+ " left join business_bill bb on bb.id=a.id"
				+ " order by bb.id desc,bb.bill_code desc";
		Session sess = null;

		List arg15;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			if (searchKey != null && !searchKey.equals("")) {
				q.setString("billCode", "%" + searchKey + "%");
				q.setString("estName", "%" + searchKey + "%");
				q.setString("nameSpell", "%" + searchKey + "%");
				q.setString("content", "%" + searchKey + "%");
				q.setString("designerName", "%" + searchKey + "%");
			}

			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg15 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg15;
	}

	public int getListForAccessoryCount(String searchKey, long estManagerId,
			long tsId, UserBean ub, Date allowStart, String busiUplo)
			throws Exception {
		String str = "";
		if (searchKey != null) {
			searchKey = searchKey.trim();
		}

		if (searchKey != null && !searchKey.equals("")) {
			str = " and ((b.bill_code like :billCode)  or (e.est_name like :estName) or (e.name_spell like :nameSpell) or (b.content like :content) or (b.designer_name_str like :designerName))";
		}

		if (tsId != 0L) {
			str = str + " and b.tally_set_id=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tally_set_id",
							ub, true);
		}

		if (estManagerId != 0L) {
			str = str + " and e.manager_id=" + estManagerId;
		}

		if (allowStart != null) {
			str = str + " and b.order_date>=:allowStart";
		}

		if (busiUplo.equals("UNMAK")) {
			str = str + " and be.examined=\'Y\' and ie.maked=\'N\'";
		} else {
			str = str + " and be.examined=\'N\'";
		}

		String sql = "select count(b.id) from business_bill_item i left join business_bill_item_ext ie on i.id=ie.id left join business_bill b on b.id=i.bill_id left join business_bill_ext be on b.id=be.id left join establishment e on e.id=b.client_id where be.enabled=\'Y\' and be.closed=\'N\' and i.wc_id!=0"
				+ str + " group by i.wc_id,b.id,e.est_name";
		Session sess = null;

		int arg14;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			if (searchKey != null && !searchKey.equals("")) {
				q.setString("billCode", "%" + searchKey + "%");
				q.setString("estName", "%" + searchKey + "%");
				q.setString("nameSpell", "%" + searchKey + "%");
				q.setString("content", "%" + searchKey + "%");
				q.setString("designerName", "%" + searchKey + "%");
			}

			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			List list = q.list();
			if (list.isEmpty()) {
				return 0;
			}

			arg14 = Integer.parseInt(list.get(0).toString());
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg14;
	}

	public List<?> getListForExamine(String searchKey, String costType,
			long estManagerId, long tsId, UserBean ub, Date allowStart,
			int startIndex, int num) throws Exception {
		String str = this.getBlearSearchHql(searchKey);
		if (costType != null) {
			if (costType.equals("NOT_ZERO")) {
				str = str + " and b.cost!=0";
			} else if (costType.equals("ZERO")) {
				str = str + " and b.cost=0";
			}
		}

		if (estManagerId != 0L) {
			str = str + " and e.managerId=" + estManagerId;
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		if (allowStart != null) {
			str = str + " and b.orderDate>=:allowStart";
		}

		String sql = "select b,e.estName from com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be,com.minierp.model.Establishment e where b.id=be.id and b.clientId=e.id"
				+ str
				+ " and be.enabled=\'Y\'"
				+ " and be.examined=\'N\'"
				+ " order by b.id desc,b.billCode desc";
		Session sess = null;

		List arg15;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			this.setBlearSearchValue(q, searchKey);
			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg15 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg15;
	}

	public int getListForExamineCount(String searchKey, String costType,
			long estManagerId, long tsId, UserBean ub, Date allowStart)
			throws Exception {
		String str = this.getBlearSearchHql(searchKey);
		if (costType != null) {
			if (costType.equals("NOT_ZERO")) {
				str = str + " and b.cost!=0";
			} else if (costType.equals("ZERO")) {
				str = str + " and b.cost=0";
			}
		}

		if (estManagerId != 0L) {
			str = str + " and e.managerId=" + estManagerId;
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		if (allowStart != null) {
			str = str + " and b.orderDate>=:allowStart";
		}

		String sql = "select count(b.id) from com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be,com.minierp.model.Establishment e where b.id=be.id and b.clientId=e.id"
				+ str + " and be.enabled=\'Y\'" + " and be.examined=\'N\'";
		Session sess = null;

		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			this.setBlearSearchValue(q, searchKey);
			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			List list = q.list();
			if (!list.isEmpty()) {
				int arg14 = Integer.parseInt(list.get(0).toString());
				return arg14;
			}
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return 0;
	}

	public List<?> getListForUnExamine(String searchKey, long estManagerId,
			long tsId, UserBean ub, Date allowStart, int startIndex, int num)
			throws Exception {
		String str = this.getBlearSearchHql(searchKey);
		if (estManagerId != 0L) {
			str = str + " and e.managerId=" + estManagerId;
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		if (allowStart != null) {
			str = str + " and b.orderDate>=:allowStart";
		}

		String sql = "select b,e.estName from com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be,com.minierp.model.Establishment e where b.id=be.id and b.clientId=e.id"
				+ str
				+ " and be.enabled=\'Y\'"
				+ " and be.examined=\'Y\'"
				+ " and be.closed=\'N\'"
				+ " and be.maked=\'N\'"
				+ " and be.hasDeliver=\'N\'"
				+ " and be.hasReceive=\'N\'"
				+ " and be.hasAdjust=\'N\'"
				+ " order by b.id desc,b.billCode desc";
		Session sess = null;

		List arg14;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			this.setBlearSearchValue(q, searchKey);
			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg14 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg14;
	}

	public int getListForUnExamineCount(String searchKey, long estManagerId,
			long tsId, UserBean ub, Date allowStart) throws Exception {
		String str = this.getBlearSearchHql(searchKey);
		if (estManagerId != 0L) {
			str = str + " and e.managerId=" + estManagerId;
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		if (allowStart != null) {
			str = str + " and b.orderDate>=:allowStart";
		}

		String sql = "select count(b.id) from com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be,com.minierp.model.Establishment e where b.id=be.id and b.clientId=e.id"
				+ str
				+ " and be.enabled=\'Y\'"
				+ " and be.examined=\'Y\'"
				+ " and be.closed=\'N\'"
				+ " and be.maked=\'N\'"
				+ " and be.hasReceive=\'N\'"
				+ " and be.hasAdjust=\'N\'"
				+ " and be.hasDeliver=\'N\'";
		Session sess = null;

		int arg13;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			this.setBlearSearchValue(q, searchKey);
			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			List list = q.list();
			if (list.isEmpty()) {
				return 0;
			}

			arg13 = Integer.parseInt(list.get(0).toString());
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg13;
	}

	public List<?> getListForReorder(long clientId, String searchKey,
			UserBean ub, Date allowStart, int startIndex, int num)
			throws Exception {
		String str = this.getBlearSearchHql(searchKey);
		if (allowStart != null) {
			str = str + " and b.orderDate>=:allowStart";
		}

		String sql = "select b,e.estName from com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be,com.minierp.model.Establishment e where b.id=be.id and b.clientId=e.id"
				+ str
				+ " and be.enabled=\'Y\'"
				+ " and be.examined=\'Y\'"
				+ " and b.clientId="
				+ clientId
				+ EncCommonHelper
						.createTallySetSQLStr("b.tallySetId", ub, true)
				+ " order by b.id desc,b.billCode desc";
		Session sess = null;

		List arg12;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			this.setBlearSearchValue(q, searchKey);
			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg12 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg12;
	}

	public int getListForReorderCount(long clientId, String searchKey,
			UserBean ub, Date allowStart) throws Exception {
		String str = this.getBlearSearchHql(searchKey);
		if (allowStart != null) {
			str = str + " and b.orderDate>=:allowStart";
		}

		String sql = "select count(b.id) from com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be,com.minierp.model.Establishment e where b.id=be.id and b.clientId=e.id"
				+ str
				+ " and be.enabled=\'Y\'"
				+ " and be.examined=\'Y\'"
				+ " and b.clientId="
				+ clientId
				+ EncCommonHelper
						.createTallySetSQLStr("b.tallySetId", ub, true);
		Session sess = null;

		int arg11;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			this.setBlearSearchValue(q, searchKey);
			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			List list = q.list();
			if (list.isEmpty()) {
				return 0;
			}

			arg11 = Integer.parseInt(list.get(0).toString());
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg11;
	}

	public List<?> getListForClose(String searchKey, String cost,
			String status, long estManagerId, long tsId, UserBean ub,
			Date allowStart, String orderBy, int startIndex, int num)
			throws Exception {
		String str = this.getBlearSearchHql(searchKey);
		if (cost != null && cost.equals("ZERO")) {
			str = str + " and b.cost=0";
		} else if (cost != null && cost.equals("NOT_ZERO")) {
			str = str + " and b.cost!=0";
		}

		if (status != null && status.equals("COMP_DELI")) {
			str = str + " and be.completed=\'Y\' and be.delivered=\'Y\'";
		}

		if (estManagerId != 0L) {
			str = str + " and e.managerId=" + estManagerId;
		}

		if (allowStart != null) {
			str = str + " and b.orderDate>=:allowStart";
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		String sql = "select b,e.estName,be.completed,be.delivered,be.adjustCost,be.receTime from com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be,com.minierp.model.Establishment e where b.id=be.id and b.clientId=e.id"
				+ str
				+ " and be.enabled=\'Y\'"
				+ " and be.examined=\'Y\'"
				+ " and be.closed=\'N\'" + " and be.collected=\'Y\'";
		if (orderBy != null && orderBy.equals("ORDER_DATE")) {
			sql = sql + " order by b.orderDate desc,b.id desc";
		} else {
			sql = sql + " order by be.receTime desc,b.id desc";
		}

		Session sess = null;

		List arg17;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			this.setBlearSearchValue(q, searchKey);
			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg17 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg17;
	}

	public List getListForCloseCount(String searchKey, String cost,
			String status, long estManagerId, long tsId, UserBean ub,
			Date allowStart) throws Exception {
		String str = this.getBlearSearchHql(searchKey);
		if (cost != null && cost.equals("ZERO")) {
			str = str + " and b.cost=0";
		} else if (cost != null && cost.equals("NOT_ZERO")) {
			str = str + " and b.cost!=0";
		}

		if (status != null && status.equals("COMP_DELI")) {
			str = str + " and be.completed=\'Y\' and be.delivered=\'Y\'";
		}

		if (estManagerId != 0L) {
			str = str + " and e.managerId=" + estManagerId;
		}

		if (allowStart != null) {
			str = str + " and b.orderDate>=:allowStart";
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		String sql = "select count(b.id),sum(b.cost) from com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be,com.minierp.model.Establishment e where b.id=be.id and b.clientId=e.id"
				+ str
				+ " and be.enabled=\'Y\'"
				+ " and be.examined=\'Y\'"
				+ " and be.closed=\'N\'" + " and be.collected=\'Y\'";
		Session sess = null;

		List arg14;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			this.setBlearSearchValue(q, searchKey);
			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			arg14 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg14;
	}

	public List<?> getListForDeliver(String delivered, String completed,
			String timeout, String searchKey, long estManagerId, long tsId,
			UserBean ub, Date allowStart, String orderBy, int startIndex,
			int num) throws Exception {
		String str = "";
		if (searchKey != null) {
			searchKey = searchKey.trim();
		}

		if (searchKey != null && !searchKey.equals("")) {
			str = " and ((b.billCode like :searchKey)  or (e.estName like :searchKey) or (e.nameSpell like :searchKey) or (b.content like :searchKey) or (b.phone like :searchKey))";
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		if (delivered != null && !delivered.equals("")) {
			str = str + " and be.delivered=:delivered";
		} else {
			str = str + " and be.delivered!=\'Y\'";
		}

		if (completed != null && !completed.equals("")) {
			str = str + " and be.completed=:completed";
		}

		if (timeout != null && timeout.equals("Y")) {
			str = str + " and b.deliverDate<:deliverDate";
		}

		if (estManagerId != 0L) {
			str = str + " and e.managerId=" + estManagerId;
		}

		if (allowStart != null) {
			str = str + " and b.orderDate>=:allowStart";
		}

		String orderStr = " b.id desc,b.billCode desc";
		if (orderBy != null) {
			if (orderBy.equals("EXAM_DATE")) {
				orderStr = " be.examineTime desc";
			} else if (orderBy.equals("CREA_DATE")) {
				orderStr = " b.createTime desc";
			} else if (orderBy.equals("ORDE_DATE")) {
				orderStr = " b.orderDate desc";
			} else if (orderBy.equals("DELI_DATE")) {
				orderStr = " b.deliverDate desc";
			}
		}

		String sql = "select b,e.estName,be.completed,be.hasDeliver from com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be,com.minierp.model.Establishment e where b.id=be.id and b.clientId=e.id"
				+ str
				+ " and be.enabled=\'Y\'"
				+ " and be.examined=\'Y\'"
				+ " and be.closed=\'N\'" + " order by " + orderStr;
		Session sess = null;

		List arg19;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			if (searchKey != null && !searchKey.equals("")) {
				q.setString("searchKey", "%" + searchKey + "%");
			}

			if (delivered != null && !delivered.equals("")) {
				q.setString("delivered", delivered);
			}

			if (completed != null && !completed.equals("")) {
				q.setString("completed", completed);
			}

			if (timeout != null && timeout.equals("Y")) {
				q.setTimestamp("deliverDate", new Date());
			}

			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg19 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg19;
	}

	public int getListForDeliverCount(String delivered, String completed,
			String timeout, String searchKey, long estManagerId, long tsId,
			UserBean ub, Date allowStart) throws Exception {
		String str = "";
		if (searchKey != null) {
			searchKey = searchKey.trim();
		}

		if (searchKey != null && !searchKey.equals("")) {
			str = " and ((b.billCode like :searchKey)  or (e.estName like :searchKey) or (e.nameSpell like :searchKey) or (b.content like :searchKey) or (b.phone like :searchKey))";
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		if (delivered != null && !delivered.equals("")) {
			str = str + " and be.delivered=:delivered";
		} else {
			str = str + " and be.delivered!=\'Y\'";
		}

		if (completed != null && !completed.equals("")) {
			str = str + " and be.completed=:completed";
		}

		if (timeout != null && timeout.equals("Y")) {
			str = str + " and b.deliverDate<:deliverDate";
		}

		if (estManagerId != 0L) {
			str = str + " and e.managerId=" + estManagerId;
		}

		if (allowStart != null) {
			str = str + " and b.orderDate>=:allowStart";
		}

		String sql = "select count(b.id) from com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be,com.minierp.model.Establishment e where b.id=be.id and b.clientId=e.id"
				+ str
				+ " and be.enabled=\'Y\'"
				+ " and be.examined=\'Y\'"
				+ " and be.closed=\'N\'";
		Session sess = null;

		int arg16;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			if (searchKey != null && !searchKey.equals("")) {
				q.setString("searchKey", "%" + searchKey + "%");
			}

			if (delivered != null && !delivered.equals("")) {
				q.setString("delivered", delivered);
			}

			if (completed != null && !completed.equals("")) {
				q.setString("completed", completed);
			}

			if (timeout != null && timeout.equals("Y")) {
				q.setTimestamp("deliverDate", new Date());
			}

			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			List list = q.list();
			if (list.isEmpty()) {
				return 0;
			}

			arg16 = Integer.parseInt(list.get(0).toString());
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg16;
	}

	public List<?> getListForReceive(String searchKey, Date startDate,
			Date endDate, String contractCode, long pmId, long areaId,
			long estTypeId, long estLevelId, String billCode, String content,
			String linkman, String mngName, String follName, String timeouted,
			String minCost, String maxCost, String status, long estManagerId,
			long tsId, UserBean ub, int startIndex, int num) throws Exception {
		String tblStr = " com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be,com.minierp.model.Establishment e";
		String str = "";
		if (searchKey != null) {
			searchKey = searchKey.trim();
		}

		if (searchKey != null && !searchKey.equals("")) {
			str = " and (e.estName like :estName)";
		}

		if (startDate != null) {
			str = str + " and b.orderDate>=:startDate";
		}

		if (endDate != null) {
			str = str + " and b.orderDate<:endDate";
		}

		if (contractCode != null) {
			contractCode = contractCode.trim();
		}

		if (contractCode != null && !contractCode.equals("")) {
			str = str + " and (b.contractCode like :contractCode)";
		}

		if (pmId != 0L) {
			str = str + " and b.payModeId=" + pmId;
		}

		if (estTypeId != 0L) {
			str = str + " and e.estTypeId=" + estTypeId;
		}

		if (estLevelId != 0L) {
			str = str + " and e.estLevelId=" + estLevelId;
		}

		if (areaId != 0L) {
			tblStr = tblStr + ",com.minierp.model.Area a";
			str = str + " and a.id=e.areaId and (a.id=" + areaId
					+ " or (a.parentIdStr like \'%;" + areaId + ";%\'))";
		}

		if (estManagerId != 0L) {
			str = str + " and e.managerId=" + estManagerId;
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		if (billCode != null) {
			billCode = billCode.trim();
		}

		if (billCode != null && !billCode.equals("")) {
			str = str + " and (b.billCode like :billCode)";
		}

		if (content != null) {
			content = content.trim();
		}

		if (content != null && !content.equals("")) {
			str = str + " and (b.content like :content)";
		}

		if (linkman != null) {
			linkman = linkman.trim();
		}

		if (linkman != null && !linkman.equals("")) {
			str = str + " and (b.linkman like :linkman)";
		}

		if (mngName != null && !mngName.equals("")) {
			str = str + " and (b.managerName like :mngName)";
		}

		if (follName != null && !follName.equals("")) {
			str = str + " and (b.followerNameStr like :follName)";
		}

		if (timeouted != null) {
			if (timeouted.equals("Y")) {
				str = str + " and b.checkDate<:checkDate";
			} else if (timeouted.equals("N")) {
				str = str + " and b.checkDate>=:checkDate";
			}
		}

		if (status != null) {
			if (status.equals("DELIVERED")) {
				str = str + " and be.delivered!=\'N\'";
			} else if (status.equals("COMPLETED")) {
				str = str + " and be.completed=\'Y\'";
			}
		}

		if (minCost != null && !minCost.equals("")) {
			str = str + " and (b.cost-be.collectedCost+be.adjustCost)>="
					+ minCost;
		}

		if (maxCost != null && !maxCost.equals("")) {
			str = str + " and (b.cost-be.collectedCost+be.adjustCost)<"
					+ maxCost;
		}

		String sql = "select b,e.estName,be.collectedCost,be.adjustCost from"
				+ tblStr + " where b.id=be.id" + " and b.clientId=e.id" + str
				+ " and be.enabled=\'Y\'" + " and be.examined=\'Y\'"
				+ " and be.collected=\'N\'"
				+ " order by b.id desc,b.billCode desc";
		Session sess = null;

		List arg34;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			if (searchKey != null && !searchKey.equals("")) {
				q.setString("estName", "%" + searchKey + "%");
			}

			if (startDate != null) {
				q.setDate("startDate", startDate);
			}

			if (endDate != null) {
				q.setDate("endDate", endDate);
			}

			if (contractCode != null && !contractCode.equals("")) {
				q.setString("contractCode", "%" + contractCode + "%");
			}

			if (billCode != null && !billCode.equals("")) {
				q.setString("billCode", "%" + billCode + "%");
			}

			if (content != null && !content.equals("")) {
				q.setString("content", "%" + content + "%");
			}

			if (linkman != null && !linkman.equals("")) {
				q.setString("linkman", "%" + linkman + "%");
			}

			if (mngName != null && !mngName.equals("")) {
				q.setString("mngName", "%" + mngName + "%");
			}

			if (follName != null && !follName.equals("")) {
				q.setString("follName", "%" + follName + "%");
			}

			if (timeouted != null
					&& (timeouted.equals("Y") || timeouted.equals("N"))) {
				q.setDate("checkDate", new Date());
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg34 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg34;
	}

	public List getListForReceiveCount(String searchKey, Date startDate,
			Date endDate, String contractCode, long pmId, long areaId,
			long estTypeId, long estLevelId, String billCode, String content,
			String linkman, String mngName, String follName, String timeouted,
			String minCost, String maxCost, String status, long estManagerId,
			long tsId, UserBean ub) throws Exception {
		String tblStr = " com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be,com.minierp.model.Establishment e";
		String str = "";
		if (searchKey != null) {
			searchKey = searchKey.trim();
		}

		if (searchKey != null && !searchKey.equals("")) {
			str = " and (e.estName like :estName)";
		}

		if (startDate != null) {
			str = str + " and b.orderDate>=:startDate";
		}

		if (endDate != null) {
			str = str + " and b.orderDate<:endDate";
		}

		if (contractCode != null) {
			contractCode = contractCode.trim();
		}

		if (contractCode != null && !contractCode.equals("")) {
			str = str + " and (b.contractCode like :contractCode)";
		}

		if (pmId != 0L) {
			str = str + " and b.payModeId=" + pmId;
		}

		if (estTypeId != 0L) {
			str = str + " and e.estTypeId=" + estTypeId;
		}

		if (estLevelId != 0L) {
			str = str + " and e.estLevelId=" + estLevelId;
		}

		if (areaId != 0L) {
			tblStr = tblStr + ",com.minierp.model.Area a";
			str = str + " and a.id=e.areaId and (a.id=" + areaId
					+ " or (a.parentIdStr like \'%;" + areaId + ";%\'))";
		}

		if (estManagerId != 0L) {
			str = str + " and e.managerId=" + estManagerId;
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		if (billCode != null) {
			billCode = billCode.trim();
		}

		if (billCode != null && !billCode.equals("")) {
			str = str + " and (b.billCode like :billCode)";
		}

		if (content != null) {
			content = content.trim();
		}

		if (content != null && !content.equals("")) {
			str = str + " and (b.content like :content)";
		}

		if (linkman != null) {
			linkman = linkman.trim();
		}

		if (linkman != null && !linkman.equals("")) {
			str = str + " and (b.linkman like :linkman)";
		}

		if (mngName != null && !mngName.equals("")) {
			str = str + " and (b.managerName like :mngName)";
		}

		if (follName != null && !follName.equals("")) {
			str = str + " and (b.followerNameStr like :follName)";
		}

		if (timeouted != null) {
			if (timeouted.equals("Y")) {
				str = str + " and b.checkDate<:checkDate";
			} else if (timeouted.equals("N")) {
				str = str + " and b.checkDate>=:checkDate";
			}
		}

		if (status != null) {
			if (status.equals("DELIVERED")) {
				str = str + " and be.delivered!=\'N\'";
			} else if (status.equals("COMPLETED")) {
				str = str + " and be.completed=\'Y\'";
			}
		}

		if (minCost != null && !minCost.equals("")) {
			str = str + " and (b.cost-be.collectedCost+be.adjustCost)>="
					+ minCost;
		}

		if (maxCost != null && !maxCost.equals("")) {
			str = str + " and (b.cost-be.collectedCost+be.adjustCost)<"
					+ maxCost;
		}

		String sql = "select count(b.id),sum(b.cost),sum(be.collectedCost),sum(be.adjustCost) from"
				+ tblStr
				+ " where b.id=be.id"
				+ " and b.clientId=e.id"
				+ str
				+ " and be.enabled=\'Y\'"
				+ " and be.examined=\'Y\'"
				+ " and be.collected=\'N\'";
		Session sess = null;

		List arg32;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			if (searchKey != null && !searchKey.equals("")) {
				q.setString("estName", "%" + searchKey + "%");
			}

			if (startDate != null) {
				q.setDate("startDate", startDate);
			}

			if (endDate != null) {
				q.setDate("endDate", endDate);
			}

			if (contractCode != null && !contractCode.equals("")) {
				q.setString("contractCode", "%" + contractCode + "%");
			}

			if (billCode != null && !billCode.equals("")) {
				q.setString("billCode", "%" + billCode + "%");
			}

			if (content != null && !content.equals("")) {
				q.setString("content", "%" + content + "%");
			}

			if (linkman != null && !linkman.equals("")) {
				q.setString("linkman", "%" + linkman + "%");
			}

			if (mngName != null && !mngName.equals("")) {
				q.setString("mngName", "%" + mngName + "%");
			}

			if (follName != null && !follName.equals("")) {
				q.setString("follName", "%" + follName + "%");
			}

			if (timeouted != null
					&& (timeouted.equals("Y") || timeouted.equals("N"))) {
				q.setDate("checkDate", new Date());
			}

			arg32 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg32;
	}

	public List<?> getListForReceive(long clientId, long tallySetId,
			Date startDate, Date endDate) {
		String str = "";
		if (startDate != null) {
			str = str + " and b.orderDate>=:startDate";
		}

		if (endDate != null) {
			str = str + " and b.orderDate<:endDate";
		}

		String sql = "select b,be from  com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be where b.id=be.id and b.clientId="
				+ clientId
				+ str
				+ " and be.enabled=\'Y\'"
				+ " and be.examined=\'Y\'"
				+ " and be.collected=\'N\'"
				+ " and be.closed=\'N\'" + " and b.tallySetId=" + tallySetId;
		Session sess = null;

		List arg11;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			if (startDate != null) {
				q.setDate("startDate", startDate);
			}

			if (endDate != null) {
				q.setDate("endDate", endDate);
			}

			arg11 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg11;
	}

	public List<?> getList(long[] ids) {
		if (ids != null && ids.length != 0) {
			String tmpStr = "";

			for (int hql = 0; hql < ids.length; ++hql) {
				if (!tmpStr.equals("")) {
					tmpStr = tmpStr + ",";
				}

				tmpStr = tmpStr + ids[hql];
			}

			String arg3 = "from com.minierp.model.BusinessBill b where b.id in ("
					+ tmpStr + ") order by b.id desc";
			return this.getHibernateTemplate().find(arg3);
		} else {
			return new ArrayList();
		}
	}

	public BusinessBill get(String billCode) {
		String sql = "select b from  com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be where b.id=be.id and b.billCode = :billCode and be.enabled=\'Y\' order by b.id";
		String[] paramNames = new String[]{"billCode"};
		Object[] values = new Object[]{billCode};
		List list = this.getHibernateTemplate().findByNamedParam(sql,
				paramNames, values);
		return list.isEmpty() ? null : (BusinessBill) list.get(0);
	}

	public List<?> getReceivableList(String searchKey, long areaId,
			long estTypeId, long estLevelId, String creType, long estManagerId,
			Date endDate, long tsId, UserBean ub, Date allowStart, String cost,
			int startIndex, int num) throws Exception {
		String tblStr = " finance_detail f,establishment e";
		String str = "";
		if (searchKey != null) {
			searchKey = searchKey.trim();
		}

		if (searchKey != null && !searchKey.equals("")) {
			str = " and ((e.est_code like :est_code)  or (e.est_name like :est_name) or (e.name_spell like :name_spell))";
		}

		if (estTypeId != 0L) {
			str = str + " and e.est_type_id=" + estTypeId;
		}

		if (estLevelId != 0L) {
			str = str + " and e.est_level_id=" + estLevelId;
		}

		if (areaId != 0L) {
			tblStr = tblStr + ",area a";
			str = str + " and a.id=e.area_id and (a.id=" + areaId
					+ " or (a.parent_id_str like \'%;" + areaId + ";%\'))";
		}

		if (estManagerId != 0L) {
			str = str + " and e.manager_id=" + estManagerId;
		}

		if (endDate != null) {
			str = str + " and f.bill_date<:bill_date";
		}

		if (tsId != 0L) {
			str = str + " and f.tally_set_id=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("f.tally_set_id",
							ub, true);
		}

		if (allowStart != null) {
			str = str + " and f.bill_date>=:allowStart";
		}

		String creCostStr = "";
		if (creType != null && !creType.equals("")) {
			if (creType.equals("Y")) {
				creCostStr = " and rem_cost>=credit_cost";
			} else if (creType.equals("N")) {
				creCostStr = " and rem_cost<credit_cost";
			}
		}

		String costStr = " rem_cost!=0";
		if (cost.equals("ZERO")) {
			costStr = " rem_cost=0";
		}

		String sql = "select * from(select e.id,e.est_code,e.est_name,e.credit_cost,sum(f.con_cost) as con_cost,sum(f.adj_cost) as adj_cost,sum(f.rp_cost) as rp_cost,sum(f.rem_cost) as rem_cost,e.bus_time,e.status,e.mark,e.linkman,e.linkman_phone from"
				+ tblStr
				+ " where f.est_id=e.id"
				+ " and f.finance_type=\'R\'"
				+ str
				+ " group by e.id"
				+ ") aa"
				+ " where "
				+ costStr
				+ creCostStr + " order by rem_cost desc";
		Session sess = null;

		List arg26;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			if (searchKey != null && !searchKey.equals("")) {
				q.setString("est_code", "%" + searchKey + "%");
				q.setString("est_name", "%" + searchKey + "%");
				q.setString("name_spell", "%" + searchKey + "%");
			}

			if (endDate != null) {
				q.setDate("bill_date", endDate);
			}

			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg26 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg26;
	}

	public List<?> getReceivableListCount(String searchKey, long areaId,
			long estTypeId, long estLevelId, String creType, long estManagerId,
			Date endDate, long tsId, UserBean ub, Date allowStart, String cost)
			throws Exception {
		String tblStr = " finance_detail f,establishment e";
		String str = "";
		if (searchKey != null) {
			searchKey = searchKey.trim();
		}

		if (searchKey != null && !searchKey.equals("")) {
			str = " and ((e.est_code like :est_code)  or (e.est_name like :est_name) or (e.name_spell like :name_spell))";
		}

		if (estTypeId != 0L) {
			str = str + " and e.est_type_id=" + estTypeId;
		}

		if (estLevelId != 0L) {
			str = str + " and e.est_level_id=" + estLevelId;
		}

		if (areaId != 0L) {
			tblStr = tblStr + ",area a";
			str = str + " and a.id=e.area_id and (a.id=" + areaId
					+ " or (a.parent_id_str like \'%;" + areaId + ";%\'))";
		}

		if (estManagerId != 0L) {
			str = str + " and e.manager_id=" + estManagerId;
		}

		if (endDate != null) {
			str = str + " and f.bill_date<:bill_date";
		}

		if (tsId != 0L) {
			str = str + " and f.tally_set_id=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("f.tally_set_id",
							ub, true);
		}

		if (allowStart != null) {
			str = str + " and f.bill_date>=:allowStart";
		}

		String creCostStr = "";
		if (creType != null && !creType.equals("")) {
			if (creType.equals("Y")) {
				creCostStr = " and rem_cost>=credit_cost";
			} else if (creType.equals("N")) {
				creCostStr = " and rem_cost<credit_cost";
			}
		}

		String costStr = " rem_cost!=0";
		if (cost.equals("ZERO")) {
			costStr = " rem_cost=0";
		}

		String sql = " select count(id),sum(con_cost) as con_cost,sum(adj_cost) as adj_cost,sum(rp_cost) as rp_cost,sum(rem_cost) as rem_cost from( select e.id,sum(f.con_cost) as con_cost,sum(f.adj_cost) as adj_cost,sum(f.rp_cost) as rp_cost,sum(f.rem_cost) as rem_cost,e.credit_cost from"
				+ tblStr
				+ " where f.est_id=e.id"
				+ " and f.finance_type=\'R\'"
				+ str
				+ " group by e.id"
				+ ") aa"
				+ " where "
				+ costStr
				+ creCostStr;
		Session sess = null;

		List arg24;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			if (searchKey != null && !searchKey.equals("")) {
				q.setString("est_code", "%" + searchKey + "%");
				q.setString("est_name", "%" + searchKey + "%");
				q.setString("name_spell", "%" + searchKey + "%");
			}

			if (endDate != null) {
				q.setDate("bill_date", endDate);
			}

			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			arg24 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg24;
	}

	public List<?> getList(long clientId, boolean examined, UserBean ub) {
		String examineFlag = "N";
		if (examined) {
			examineFlag = "Y";
		}

		String sql = "select b from  com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be where b.id=be.id and b.clientId="
				+ clientId
				+ " and be.enabled=\'Y\'"
				+ " and be.examined=\'"
				+ examineFlag
				+ "\'"
				+ EncCommonHelper
						.createTallySetSQLStr("b.tallySetId", ub, true)
				+ " order by b.orderDate desc,b.billCode desc";
		return this.getHibernateTemplate().find(sql);
	}

	public int updatePrintNum(final long[] idArr, final String printerName) {
		Object obj = this.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						if (idArr != null && idArr.length != 0) {
							String str = "";

							for (int hql = 0; hql < idArr.length; ++hql) {
								if (!str.equals("")) {
									str = str + ",";
								}

								str = str + idArr[hql];
							}

							String arg3 = "update com.minierp.model.BusinessBillExt set printNum=printNum+1,lastPrinter=:lastPrinter where id in ("
									+ str + ")";
							return new Integer(session.createQuery(arg3)
									.setString("lastPrinter", printerName)
									.executeUpdate());
						} else {
							return Integer.valueOf(0);
						}
					}
				});
		return ((Integer) obj).intValue();
	}

	public Object[] getStatusValues(long id) {
		String sql = "select b.updateTime,b.orderDate,b.clientId,b.bankAccountId,b.imprest,b.favCost,b.cost,b.billCode,b.designerIdStr,b.followerIdStr,b.deliverDate from com.minierp.model.BusinessBill b where b.id="
				+ id;
		List list = this.getHibernateTemplate().find(sql);
		return list.size() > 0 ? (Object[]) list.get(0) : null;
	}

	public List<?> getListForStoSelect(String searchKey, long tsId,
			UserBean ub, long estManagerId, Date allowStart, int startIndex,
			int num) throws Exception {
		String str = "";
		if (searchKey != null) {
			searchKey = searchKey.trim();
		}

		if (searchKey != null && !searchKey.equals("")) {
			str = " and ((b.bill_code like :searchKey)  or (e.est_name like :searchKey) or (e.name_spell like :searchKey) or (b.content like :searchKey))";
		}

		if (tsId != 0L) {
			str = str + " and b.tally_set_id=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tally_set_id",
							ub, true);
		}

		if (estManagerId != 0L) {
			str = str + " and e.manager_id=" + estManagerId;
		}

		if (allowStart != null) {
			str = str + " and b.order_date>=:allowStart";
		}

		String sql = "select b.id,b.bill_code,b.order_date,e.est_name,b.content,b.amount,b.deliver_date,b.manager_name,aa.id as obrel_id  from business_bill b left join business_bill_ext be on b.id=be.id left join establishment e on b.client_id=e.id left join (select distinct bb.id from business_bill bb,out_bill ob where bb.bill_code=ob.business_bill_code group by bb.id) aa on aa.id=b.id where be.enabled=\'Y\' and be.examined=\'Y\' and be.closed=\'N\'"
				+ str + " order by b.id desc,b.bill_code desc";
		Session sess = null;

		List arg14;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			if (searchKey != null && !searchKey.equals("")) {
				q.setString("searchKey", "%" + searchKey + "%");
			}

			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg14 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg14;
	}

	public int getListForStoSelectCount(String searchKey, long tsId,
			UserBean ub, long estManagerId, Date allowStart) throws Exception {
		String str = "";
		if (searchKey != null) {
			searchKey = searchKey.trim();
		}

		if (searchKey != null && !searchKey.equals("")) {
			str = " and ((b.bill_code like :searchKey)  or (e.est_name like :searchKey) or (e.name_spell like :searchKey) or (b.content like :searchKey))";
		}

		if (tsId != 0L) {
			str = str + " and b.tally_set_id=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tally_set_id",
							ub, true);
		}

		if (estManagerId != 0L) {
			str = str + " and e.manager_id=" + estManagerId;
		}

		if (allowStart != null) {
			str = str + " and b.order_date>=:allowStart";
		}

		String sql = "select count(b.id) from business_bill b left join business_bill_ext be on b.id=be.id left join establishment e on b.client_id=e.id where be.enabled=\'Y\' and be.examined=\'Y\' and be.closed=\'N\'"
				+ str;
		Session sess = null;

		int arg13;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			if (searchKey != null && !searchKey.equals("")) {
				q.setString("searchKey", "%" + searchKey + "%");
			}

			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			List list = q.list();
			if (list.isEmpty()) {
				return 0;
			}

			arg13 = Integer.parseInt(list.get(0).toString());
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg13;
	}

	public List<?> getListForOutCostSelect(String searchKey, UserBean ub,
			long estManagerId, Date allowStart, int startIndex, int num)
			throws Exception {
		String str = this.getBlearSearchHql(searchKey);
		if (estManagerId != 0L) {
			str = str + " and e.managerId=" + estManagerId;
		}

		if (allowStart != null) {
			str = str + " and b.orderDate>=:allowStart";
		}

		String sql = "select b.id,b.billCode,b.orderDate,e.estName,b.content,b.amount,b.deliverDate,b.managerName from com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be,com.minierp.model.Establishment e where b.id=be.id and b.clientId=e.id"
				+ str
				+ " and be.enabled=\'Y\'"
				+ " and be.examined=\'Y\'"
				+ EncCommonHelper
						.createTallySetSQLStr("b.tallySetId", ub, true)
				+ " order by b.id desc,b.billCode desc";
		Session sess = null;

		List arg12;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			this.setBlearSearchValue(q, searchKey);
			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg12 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg12;
	}

	public int getListForOutCostSelectCount(String searchKey, UserBean ub,
			long estManagerId, Date allowStart) throws Exception {
		String str = this.getBlearSearchHql(searchKey);
		if (estManagerId != 0L) {
			str = str + " and e.managerId=" + estManagerId;
		}

		if (allowStart != null) {
			str = str + " and b.orderDate>=:allowStart";
		}

		String sql = "select count(b.id) from com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be,com.minierp.model.Establishment e where b.id=be.id and b.clientId=e.id"
				+ str
				+ " and be.enabled=\'Y\'"
				+ " and be.examined=\'Y\'"
				+ EncCommonHelper
						.createTallySetSQLStr("b.tallySetId", ub, true);
		Session sess = null;

		int arg11;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			this.setBlearSearchValue(q, searchKey);
			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			List list = q.list();
			if (list.isEmpty()) {
				return 0;
			}

			arg11 = Integer.parseInt(list.get(0).toString());
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg11;
	}

	public List<?> getSmsReceivableList(Date lastSmsDate) throws Exception {
		String sql = "select contact_name,mobile,cost,collected_cost,adjust_cost,est_id,est_name from ( select c.contact_name,c.mobile,sum(b.cost) as cost,sum(be.collected_cost) as collected_cost,sum(be.adjust_cost) as adjust_cost,e.credit_cost,e.id as est_id,e.est_name from business_bill b,business_bill_ext be,establishment e,establishment_contact c where b.id=be.id and b.client_id=e.id and e.id=c.est_id and be.enabled=\'Y\' and be.examined=\'Y\' and (c.mobile is not null) and c.mobile!=\'\' and c.is_main=\'Y\' and ((e.sms_time is null) or e.sms_time<:sms_time) and e.credit_cost>0 group by c.id,c.contact_name,c.mobile,e.credit_cost,e.id,e.est_name) aa where (cost+adjust_cost-collected_cost)>credit_cost";
		Session sess = null;

		List arg5;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			q.setTimestamp("sms_time", lastSmsDate);
			arg5 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg5;
	}

	public List<?> getSmsCost(Date startDate, Date endDate) throws Exception {
		String sql = "select count(b.id),sum(b.cost),sum(be.adjustCost),sum(b.imprest),be.examined from com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be where b.id=be.id and be.enabled=\'Y\' and b.orderDate>=:startDate and b.orderDate<:endDate group by be.examined";
		Session sess = null;

		List arg6;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			q.setDate("startDate", startDate);
			q.setDate("endDate", endDate);
			arg6 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg6;
	}

	public int getSmsCompleteTimeout(Date endDate) throws Exception {
		String sql = "select count(b.id) from com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be where b.id=be.id and be.enabled=\'Y\' and be.examined=\'Y\' and be.completed=\'N\' and be.closed=\'N\' and b.deliverDate<:endDate";
		Session sess = null;

		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			q.setDate("endDate", endDate);
			List list = q.list();
			if (!list.isEmpty() && list.get(0) != null) {
				int arg6 = Integer.parseInt(list.get(0).toString());
				return arg6;
			}
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return 0;
	}

	public int getSmsDeliverTimeout(Date endDate) throws Exception {
		String sql = "select count(b.id) from com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be where b.id=be.id and be.enabled=\'Y\' and be.examined=\'Y\' and be.delivered=\'N\' and be.closed=\'N\' and b.deliverDate<:endDate";
		Session sess = null;

		int arg6;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			q.setDate("endDate", endDate);
			List list = q.list();
			if (list.isEmpty() || list.get(0) == null) {
				return 0;
			}

			arg6 = Integer.parseInt(list.get(0).toString());
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg6;
	}

	public List<?> getSmsReceiveTimeout(Date endDate) throws Exception {
		String sql = "select count(b.id),sum(b.cost),sum(be.collectedCost),sum(be.adjustCost) from com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be where b.id=be.id and be.enabled=\'Y\' and be.examined=\'Y\' and be.collected=\'N\' and b.checkDate<:endDate";
		Session sess = null;

		List arg5;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			q.setDate("endDate", endDate);
			arg5 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg5;
	}

	public int unClose(final long id) {
		Object obj = this.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						String hql = "update com.minierp.model.BusinessBillExt set closed=\'N\' ,closerName=\'\' ,closerId=0 ,closeTime=null where id="
								+ id + " and enabled=\'Y\'";
						Query q = session.createQuery(hql);
						return new Integer(q.executeUpdate());
					}
				});
		return ((Integer) obj).intValue();
	}

	public int estCorporate(final long fromClientId, final long toClientId) {
		Object obj = this.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						String hql = "update com.minierp.model.BusinessBill set clientId="
								+ toClientId
								+ " ,updateTime=:updateTime"
								+ " where clientId=" + fromClientId;
						Query q = session.createQuery(hql);
						q.setTimestamp("updateTime", new Date());
						return new Integer(q.executeUpdate());
					}
				});
		return ((Integer) obj).intValue();
	}

	public List getExamSmsCount(long[] tsIdArr) {
		if (tsIdArr != null && tsIdArr.length != 0) {
			String sql = "select b.billCode from  com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be where b.id=be.id and be.examSms=\'Y\'"
					+ PageHelper.createIdArrStr(tsIdArr, "b.tallySetId", true);
			return this.getHibernateTemplate().find(sql);
		} else {
			return new ArrayList();
		}
	}

	public List getExamSmsCount() {
		String sql = "select b.billCode from  com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be where b.id=be.id and be.examSms=\'Y\'";
		return this.getHibernateTemplate().find(sql);
	}

	public int updateExamSms() {
		Object obj = this.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						String hql = "update com.minierp.model.BusinessBillExt set examSms=\'N\' where examSms=\'Y\'";
						Query q = session.createQuery(hql);
						return new Integer(q.executeUpdate());
					}
				});
		return ((Integer) obj).intValue();
	}

	public List getMakeSmsCount(long[] tsIdArr, long[] wcIdArr) {
		if (tsIdArr != null && tsIdArr.length != 0 && wcIdArr != null
				&& wcIdArr.length != 0) {
			String sql = "select b.billCode from  com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be,com.minierp.model.BusinessBillItem i where b.id=be.id and b.id=i.billId and be.makeSms=\'Y\'"
					+ PageHelper.createIdArrStr(tsIdArr, "b.tallySetId", true)
					+ PageHelper.createIdArrStr(wcIdArr, "i.wcId", true)
					+ " group by i.wcId,b.billCode";
			return this.getHibernateTemplate().find(sql);
		} else {
			return new ArrayList();
		}
	}

	public List getMakeSmsCount(long[] tsIdArr) {
		if (tsIdArr != null && tsIdArr.length != 0) {
			String sql = "select b.billCode from  com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be,com.minierp.model.BusinessBillItem i where b.id=be.id and b.id=i.billId and be.makeSms=\'Y\'"
					+ PageHelper.createIdArrStr(tsIdArr, "b.tallySetId", true)
					+ " group by i.wcId,b.billCode";
			return this.getHibernateTemplate().find(sql);
		} else {
			return new ArrayList();
		}
	}

	public List getMakeSmsCount() {
		String sql = "select b.billCode from  com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be where b.id=be.id and be.makeSms=\'Y\'";
		return this.getHibernateTemplate().find(sql);
	}

	public int updateMakeSms() {
		Object obj = this.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						String hql = "update com.minierp.model.BusinessBillExt set makeSms=\'N\' where makeSms=\'Y\'";
						Query q = session.createQuery(hql);
						return new Integer(q.executeUpdate());
					}
				});
		return ((Integer) obj).intValue();
	}

	public List<?> getListForFeeSelect(String searchKey, long tsId,
			UserBean ub, long estManagerId, Date allowStart, int startIndex,
			int num) throws Exception {
		String str = this.getBlearSearchHql(searchKey);
		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		if (estManagerId != 0L) {
			str = str + " and e.managerId=" + estManagerId;
		}

		if (allowStart != null) {
			str = str + " and b.orderDate>=:allowStart";
		}

		String sql = "select b.id,b.billCode,b.orderDate,e.estName,b.content,b.amount,b.deliverDate,b.managerName from com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be,com.minierp.model.Establishment e where b.id=be.id and b.clientId=e.id"
				+ str
				+ " and be.enabled=\'Y\'"
				+ " and be.closed=\'N\'"
				+ " order by b.id desc,b.billCode desc";
		Session sess = null;

		List arg14;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			this.setBlearSearchValue(q, searchKey);
			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg14 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg14;
	}

	public int getListForFeeSelectCount(String searchKey, long tsId,
			UserBean ub, long estManagerId, Date allowStart) throws Exception {
		String str = this.getBlearSearchHql(searchKey);
		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		if (estManagerId != 0L) {
			str = str + " and e.managerId=" + estManagerId;
		}

		if (allowStart != null) {
			str = str + " and b.orderDate>=:allowStart";
		}

		String sql = "select count(b.id) from com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be,com.minierp.model.Establishment e where b.id=be.id and b.clientId=e.id"
				+ str + " and be.enabled=\'Y\'" + " and be.closed=\'N\'";
		Session sess = null;

		int arg13;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			this.setBlearSearchValue(q, searchKey);
			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			List list = q.list();
			if (list.isEmpty()) {
				return 0;
			}

			arg13 = Integer.parseInt(list.get(0).toString());
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg13;
	}

	public BusinessProfit getBusinessProfit(String bbCode) {
		String sql = "from com.minierp.model.BusinessProfit b where b.bbCode = :bbCode order by b.id";
		String[] paramNames = new String[]{"bbCode"};
		Object[] values = new Object[]{bbCode};
		List list = this.getHibernateTemplate().findByNamedParam(sql,
				paramNames, values);
		return list.isEmpty() ? null : (BusinessProfit) list.get(0);
	}

	public BusinessBill getByItemId(long itemId) {
		String sql = "select b from com.minierp.model.BusinessBill b ,com.minierp.model.BusinessBillItem i where b.id = i.billId and i.id="
				+ itemId;
		List list = this.getHibernateTemplate().find(sql);
		return list.isEmpty() ? null : (BusinessBill) list.get(0);
	}

	public List<?> getListForModifySelf(String searchKey, long tsId,
			UserBean ub, int startIndex, int num) throws Exception {
		String str = this.getBlearSearchHql(searchKey);
		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		String sql = "select b,e.estName from com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be,com.minierp.model.Establishment e where b.id=be.id and b.clientId=e.id"
				+ str
				+ " and be.enabled=\'Y\'"
				+ " and be.examined=\'N\'"
				+ " and b.createrId="
				+ ub.getStaffer().getId()
				+ " order by b.id desc,b.billCode desc";
		Session sess = null;

		List arg11;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			this.setBlearSearchValue(q, searchKey);
			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg11 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg11;
	}

	public int getListForModifySelfCount(String searchKey, long tsId,
			UserBean ub) throws Exception {
		String str = this.getBlearSearchHql(searchKey);
		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		String sql = "select count(b.id) from com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be,com.minierp.model.Establishment e where b.id=be.id and b.clientId=e.id"
				+ str
				+ " and be.enabled=\'Y\'"
				+ " and be.examined=\'N\'"
				+ " and b.createrId=" + ub.getStaffer().getId();
		Session sess = null;

		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			this.setBlearSearchValue(q, searchKey);
			List list = q.list();
			if (!list.isEmpty()) {
				int arg10 = Integer.parseInt(list.get(0).toString());
				return arg10;
			}
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return 0;
	}

	public List<?> getMonthCost(Date startDate, Date endDate, long tsId,
			UserBean ub, long managerId, Date allowStart) throws Exception {
		String str = "";
		if (managerId != 0L) {
			str = str + " and e.manager_id=" + managerId;
		}

		if (tsId != 0L) {
			str = str + " and b.tally_set_id=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tally_set_id",
							ub, true);
		}

		String sql = "select month(b.order_date),sum(b.cost),sum(be.adjust_cost),sum(be.collected_cost) from business_bill b,business_bill_ext be ,establishment e where b.id=be.id and e.id=b.client_id and be.enabled=\'Y\' and be.examined=\'Y\'"
				+ str
				+ " and b.order_date>=:startDate"
				+ " and b.order_date<:endDate"
				+ " group by month(b.order_date)";
		Session sess = null;

		List arg13;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			if (allowStart != null && allowStart.after(startDate)) {
				q.setDate("startDate", allowStart);
			} else {
				q.setDate("startDate", startDate);
			}

			q.setDate("endDate", endDate);
			arg13 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg13;
	}

	public BigDecimal getCost(long tsId, UserBean ub, Date startDate,
			Date endDate) throws Exception {
		String str = "";
		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		String sql = "select sum(b.cost) from com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be where b.id=be.id and be.enabled=\'Y\' and be.examined=\'Y\' and b.orderDate>=:startDate and b.orderDate<:endDate"
				+ str;
		Session sess = null;

		BigDecimal arg11;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			q.setDate("startDate", startDate);
			q.setDate("endDate", endDate);
			List list = q.list();
			if (!list.isEmpty() && list.get(0) != null) {
				arg11 = new BigDecimal(list.get(0).toString());
				return arg11;
			}

			arg11 = new BigDecimal(0);
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg11;
	}

	public List getBaImprest(long tsId, UserBean ub, Date startDate,
			Date endDate) throws Exception {
		String str = "";
		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		String sql = "select sum(b.imprest),b.bankAccountId from com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be where b.id=be.id and be.enabled=\'Y\' and be.examined=\'Y\' and b.orderDate>=:startDate and b.orderDate<:endDate"
				+ str + " group by b.bankAccountId";
		Session sess = null;

		List arg10;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			q.setDate("startDate", startDate);
			q.setDate("endDate", endDate);
			arg10 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg10;
	}

	public List<?> getListForAccountRec(long clientId, Date startDate,
			Date endDate, String linkman, long tallySetId, String type) {
		String sql = "select b,be from  com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be where b.id=be.id and b.clientId="
				+ clientId
				+ " and be.enabled=\'Y\'"
				+ " and be.examined=\'Y\'"
				+ " and be.collected=\'N\'"
				+ " and be.closed=\'N\'"
				+ " and b.tallySetId=" + tallySetId;
		if (type != null && type.equals("JUST_NORECE")) {
			sql = sql + " and be.hasReceive=\'N\'";
		}

		if (startDate != null) {
			sql = sql + " and b.orderDate>=:startDate";
		}

		if (endDate != null) {
			sql = sql + " and b.orderDate<:endDate";
		}

		if (linkman != null && !linkman.equals("")) {
			sql = sql + " and (b.linkman like :linkman)";
		}

		Session sess = null;

		List arg12;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			if (startDate != null) {
				q.setDate("startDate", startDate);
			}

			if (endDate != null) {
				q.setDate("endDate", endDate);
			}

			if (linkman != null && !linkman.equals("")) {
				q.setString("linkman", "%" + linkman + "%");
			}

			arg12 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg12;
	}

	public boolean isHasBill(long clientId) {
		String sql = "select b.id from business_bill b,business_bill_ext be where b.id=be.id and be.enabled=\'Y\' and b.client_id="
				+ clientId + " limit 1";
		Session sess = null;

		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			List list = q.list();
			if (list.size() > 0) {
				return true;
			}
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return false;
	}

	public List<?> getEstBusinessCountTotal(Date startDate, Date endDate,
			long estId, long areaId, long etId, long elId, long mngId,
			long tsId, UserBean ub, BigDecimal minCost, BigDecimal maxCost)
			throws Exception {
		String str = this.getEstBusinessCountSql(startDate, endDate, estId,
				areaId, etId, elId, mngId, tsId, ub);
		String tblStr = " business_bill b,business_bill_ext be,establishment e";
		if (areaId != 0L) {
			tblStr = tblStr + ",area a";
		}

		String whereStr = "";
		if (minCost != null) {
			whereStr = " where cost>=" + minCost + " and cost<" + maxCost;
		}

		String sql = "select count(*),sum(cost),sum(fav_cost),sum(adjust_cost),sum(collected_cost) from ( select sum(b.cost) as cost,sum(b.fav_cost) as fav_cost,sum(be.adjust_cost) as adjust_cost,sum(be.collected_cost) as collected_cost from"
				+ tblStr
				+ " where b.id=be.id"
				+ " and b.client_id=e.id"
				+ " and be.enabled=\'Y\'"
				+ " and be.examined=\'Y\'"
				+ str
				+ " group by e.id" + ") aa" + whereStr;
		Session sess = null;

		List arg24;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			this.setEstBusinessCountPara(q, startDate, endDate);
			arg24 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg24;
	}

	public List<?> getEstBusinessCountList(Date startDate, Date endDate,
			long estId, long areaId, long etId, long elId, long mngId,
			long tsId, UserBean ub, BigDecimal minCost, BigDecimal maxCost,
			int startIndex, int num) throws Exception {
		String str = this.getEstBusinessCountSql(startDate, endDate, estId,
				areaId, etId, elId, mngId, tsId, ub);
		String tblStr = " business_bill b,business_bill_ext be,establishment e";
		if (areaId != 0L) {
			tblStr = tblStr + ",area a";
		}

		String whereStr = "";
		if (minCost != null) {
			whereStr = " and aa.cost>=" + minCost + " and aa.cost<" + maxCost;
		}

		String sql = "select aa.cost,aa.fav_cost,aa.adjust_cost,aa.collected_cost,ee.id,ee.est_name,ee.area_id,ee.est_type_id,ee.est_level_id,ee.manager_name,ee.linkman,ee.linkman_phone from ( select e.id as est_id,sum(b.cost) as cost,sum(b.fav_cost) as fav_cost,sum(be.adjust_cost) as adjust_cost,sum(be.collected_cost) as collected_cost from"
				+ tblStr
				+ " where b.id=be.id"
				+ " and b.client_id=e.id"
				+ " and be.enabled=\'Y\'"
				+ " and be.examined=\'Y\'"
				+ str
				+ " group by e.id"
				+ ") aa,establishment ee where aa.est_id=ee.id"
				+ whereStr
				+ " order by aa.cost desc";
		Session sess = null;

		List arg26;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			this.setEstBusinessCountPara(q, startDate, endDate);
			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg26 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg26;
	}

	private String getEstBusinessCountSql(Date startDate, Date endDate,
			long estId, long areaId, long etId, long elId, long mngId,
			long tsId, UserBean ub) {
		String str = "";
		if (startDate != null) {
			str = str + " and b.order_date>=:startDate";
		}

		if (endDate != null) {
			str = str + " and b.order_date<:endDate";
		}

		if (estId != 0L) {
			str = str + " and e.id=" + estId;
		}

		if (areaId != 0L) {
			str = str + " and a.id=e.area_id and (a.id=" + areaId
					+ " or (a.parent_id_str like \'%;" + areaId + ";%\'))";
		}

		if (etId != 0L) {
			str = str + " and e.est_type_id=" + etId;
		}

		if (elId != 0L) {
			str = str + " and e.est_level_id=" + elId;
		}

		if (tsId != 0L) {
			str = str + " and b.tally_set_id=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tally_set_id",
							ub, true);
		}

		if (mngId != 0L) {
			str = str + " and e.manager_id=" + mngId;
		}

		return str;
	}

	private void setEstBusinessCountPara(SQLQuery q, Date startDate,
			Date endDate) {
		if (startDate != null) {
			q.setDate("startDate", startDate);
		}

		if (endDate != null) {
			q.setDate("endDate", endDate);
		}

	}

	public List<?> getBusinessDateCount(Date startDate, Date endDate,
			long tsId, UserBean ub) throws Exception {
		String str = "";
		if (tsId != 0L) {
			str = str + " and b.tally_set_id=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tally_set_id",
							ub, true);
		}

		String sql = "select count(b.id),sum(b.cost),sum(b.imprest) from business_bill b,business_bill_ext be where b.id=be.id and be.examined=\'Y\' and be.enabled=\'Y\' and b.order_date>=:startDate and b.order_date<:endDate"
				+ str;
		Session sess = null;

		List arg10;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			q.setDate("startDate", startDate);
			q.setDate("endDate", endDate);
			arg10 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg10;
	}

	public int getBusinessDateCountImpNum(Date startDate, Date endDate,
			long tsId, UserBean ub) throws Exception {
		String str = "";
		if (tsId != 0L) {
			str = str + " and b.tally_set_id=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tally_set_id",
							ub, true);
		}

		String sql = "select count(b.id) from business_bill b,business_bill_ext be where b.id=be.id and be.examined=\'Y\' and be.enabled=\'Y\' and b.order_date>=:startDate and b.order_date<:endDate"
				+ str + " and b.imprest!=0";
		Session sess = null;

		int arg11;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			q.setDate("startDate", startDate);
			q.setDate("endDate", endDate);
			List list = q.list();
			if (list == null || list.isEmpty() || list.get(0) == null) {
				return 0;
			}

			arg11 = Integer.parseInt(list.get(0).toString());
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg11;
	}

	public List<?> getStafferExploitList(Date startDate, Date endDate,
			long mngId, long depId, long tsId, UserBean ub, String orderBy,
			String ascOrDesc) throws Exception {
		String busStr = "";
		String recStr = "";
		String whereStr = "";
		String orderStr = "";
		if (startDate != null) {
			busStr = busStr + " and b.order_date>=:startDate";
			recStr = recStr + " and b.receive_date>=:startDate";
		}

		if (endDate != null) {
			busStr = busStr + " and b.order_date<:endDate";
			recStr = recStr + " and b.receive_date<:endDate";
		}

		if (mngId != 0L) {
			busStr = busStr + " and b.manager_id=" + mngId;
			recStr = recStr + " and bb.manager_id=" + mngId;
		}

		if (tsId != 0L) {
			busStr = busStr + " and b.tally_set_id=" + tsId;
			recStr = recStr + " and b.tally_set_id=" + tsId;
		} else {
			busStr = busStr
					+ EncCommonHelper.createTallySetSQLStr("b.tally_set_id",
							ub, true);
			recStr = recStr
					+ EncCommonHelper.createTallySetSQLStr("b.tally_set_id",
							ub, true);
		}

		if (depId != 0L) {
			whereStr = whereStr + " and (d.id=" + depId
					+ " or d.parent_id_str like \'%;" + depId + ";%\')";
		}

		orderStr = " order by a.cost desc";
		if (orderBy != null && !orderBy.equals("")) {
			if (orderBy.equals("BACK_COST")) {
				orderStr = " order by a.back_cost";
			} else if (orderBy.equals("AMOUNT")) {
				orderStr = " order by a.amount";
			} else if (orderBy.equals("LENGTH")) {
				orderStr = " order by a.length";
			} else if (orderBy.equals("SQUARE")) {
				orderStr = " order by a.square";
			} else {
				orderStr = " order by a.cost";
			}

			orderStr = orderStr + " " + ascOrDesc;
		}

		String sql = "select s.id,s.staffer_name,d.department_name,a.cost,a.imp_cost,a.rec_cost,a.back_cost,a.amount,(a.leng_m+a.leng_cm/100) as length,a.square from (select manager_id,sum(cost) as cost,sum(imp_cost) as imp_cost,sum(rec_cost) as rec_cost,sum(imp_cost+rec_cost) as back_cost,sum(amount) as amount,sum(leng_m) as leng_m,sum(leng_cm) as leng_cm,sum(square) as square from ( select b.manager_id,b.cost,b.imprest as imp_cost,0 as rec_cost,b.amount,b.leng_m,b.leng_cm,b.square from business_bill b,business_bill_ext be where b.id=be.id and be.enabled=\'Y\' and be.examined=\'Y\'"
				+ busStr
				+ " union all "
				+ " select bb.manager_id,0 as cost,0 as imp_cost,i.this_cost as rec_cost,0 as amount,0 as leng_m,0 as leng_cm,0 as square from"
				+ " business_bill bb,receive_bill b,receive_bill_item i"
				+ " where b.id=i.bill_id"
				+ " and bb.id=i.business_bill_id"
				+ " and b.enabled=\'Y\'"
				+ " and b.examined=\'Y\'"
				+ recStr
				+ ") aa group by manager_id"
				+ ") a,staffer s,department d where a.manager_id=s.id and d.id=s.department_id "
				+ whereStr + orderStr;
		Session sess = null;

		List arg19;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			if (startDate != null) {
				q.setDate("startDate", startDate);
			}

			if (endDate != null) {
				q.setDate("endDate", endDate);
			}

			arg19 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg19;
	}

	public List getMtBusinessCountTotal(Date startDate, Date endDate,
			long mtId, String proName, long estId, String mngName,
			String desiName, String follName, String creaName, long mngId,
			long tsId, UserBean ub) throws Exception {
		String str = this.getMtBusinessCountSql(startDate, endDate, mtId,
				proName, estId, mngName, desiName, follName, creaName, mngId,
				tsId, ub);
		String joinEstStr = "";
		if (mngId != 0L) {
			joinEstStr = " left join establishment e on b.client_id=e.id";
		}

		String sql = "select count(*),sum(cost) from ( select t.id,sum(i.cost) as cost from business_bill b left join business_bill_ext be on b.id=be.id left join business_bill_item i on b.id=i.bill_id left join make_type t on i.make_type_id=t.id"
				+ joinEstStr
				+ " where be.enabled=\'Y\'"
				+ " and be.examined=\'Y\'"
				+ " and (i.price_type is not null)"
				+ str
				+ " group by t.id,i.pro_name,i.unit,i.price_type,i.leng_type"
				+ ") a";
		Session sess = null;

		List arg22;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			this.setMtBusinessCountPara(q, startDate, endDate, proName,
					mngName, desiName, follName, creaName);
			arg22 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg22;
	}

	public List<?> getMtBusinessCountList(Date startDate, Date endDate,
			long mtId, String proName, long estId, String mngName,
			String desiName, String follName, String creaName, long mngId,
			long tsId, UserBean ub, int startIndex, int num) throws Exception {
		String str = this.getMtBusinessCountSql(startDate, endDate, mtId,
				proName, estId, mngName, desiName, follName, creaName, mngId,
				tsId, ub);
		String joinEstStr = "";
		if (mngId != 0L) {
			joinEstStr = " left join establishment e on b.client_id=e.id";
		}

		String sql = " select t.type_name,i.pro_name,i.unit,0 as i_amount,0 as u_price,sum(i.cost),i.price_type,sum(i.amount),sum(i.square_num),t.enabled,t.id,i.leng_type from business_bill b left join business_bill_ext be on b.id=be.id left join business_bill_item i on b.id=i.bill_id left join make_type t on i.make_type_id=t.id"
				+ joinEstStr
				+ " where be.enabled=\'Y\'"
				+ " and be.examined=\'Y\'"
				+ " and (i.price_type is not null)"
				+ str
				+ " group by t.id,i.pro_name,i.unit,i.price_type,i.leng_type"
				+ " order by t.enabled,t.type_name";
		Session sess = null;

		List arg24;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			this.setMtBusinessCountPara(q, startDate, endDate, proName,
					mngName, desiName, follName, creaName);
			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg24 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg24;
	}

	private String getMtBusinessCountSql(Date startDate, Date endDate,
			long mtId, String proName, long estId, String mngName,
			String desiName, String follName, String creaName, long mngId,
			long tsId, UserBean ub) {
		String str = "";
		if (startDate != null) {
			str = str + " and b.order_date>=:startDate";
		}

		if (endDate != null) {
			str = str + " and b.order_date<:endDate";
		}

		if (mtId != 0L) {
			str = str + " and (t.id=" + mtId + " or (t.parent_id_str like \'%;"
					+ mtId + ";%\'))";
		}

		if (proName != null) {
			proName = proName.trim();
		}

		if (proName != null && !proName.equals("")) {
			str = str + " and (i.pro_name like :proName)";
		}

		if (estId != 0L) {
			str = str + " and b.client_id=" + estId;
		}

		if (mngName != null && !mngName.equals("")) {
			str = str + " and b.manager_name=:mngName";
		}

		if (desiName != null && !desiName.equals("")) {
			str = str + " and (b.designer_name_str like :desiName)";
		}

		if (follName != null && !follName.equals("")) {
			str = str + " and (b.follower_name_str like :follName)";
		}

		if (creaName != null && !creaName.equals("")) {
			str = str + " and b.creater_name=:creaName";
		}

		if (mngId != 0L) {
			str = str + " and e.manager_id=" + mngId;
		}

		if (tsId != 0L) {
			str = str + " and b.tally_set_id=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tally_set_id",
							ub, true);
		}

		return str;
	}

	private void setMtBusinessCountPara(SQLQuery q, Date startDate,
			Date endDate, String proName, String mngName, String desiName,
			String follName, String creaName) {
		if (startDate != null) {
			q.setDate("startDate", startDate);
		}

		if (endDate != null) {
			q.setDate("endDate", endDate);
		}

		if (proName != null) {
			proName = proName.trim();
		}

		if (proName != null && !proName.equals("")) {
			q.setString("proName", "%" + proName + "%");
		}

		if (mngName != null && !mngName.equals("")) {
			q.setString("mngName", mngName);
		}

		if (desiName != null && !desiName.equals("")) {
			q.setString("desiName", "%" + desiName + "%");
		}

		if (follName != null && !follName.equals("")) {
			q.setString("follName", "%" + follName + "%");
		}

		if (creaName != null && !creaName.equals("")) {
			q.setString("creaName", creaName);
		}

	}

	public int clearDesignDetail(final long billId) {
		Object obj = this.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						String hql = "delete com.minierp.model.DesignDetail where billId = :billId";
						return new Integer(session.createQuery(hql)
								.setLong("billId", billId).executeUpdate());
					}
				});
		return ((Integer) obj).intValue();
	}

	public int clearFollowDetail(final long billId) {
		Object obj = this.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						String hql = "delete com.minierp.model.FollowDetail where billId = :billId";
						return new Integer(session.createQuery(hql)
								.setLong("billId", billId).executeUpdate());
					}
				});
		return ((Integer) obj).intValue();
	}

	public List getReceivableAgeList(String estName, long areaId,
			long estTypeId, long estLevelId, long estManagerId, long tsId,
			UserBean ub, Date yearDate) throws Exception {
		String tblStr = "business_bill f,business_bill_ext be,establishment e";
		String str = "";
		String tsStr = "";
		if (estName != null) {
			estName = estName.trim();
		}

		if (estName != null && !estName.equals("")) {
			str = str
					+ " and ((e.est_name like :estName) or (e.name_spell like :estName))";
		}

		if (estTypeId != 0L) {
			str = str + " and e.est_type_id=" + estTypeId;
		}

		if (estLevelId != 0L) {
			str = str + " and e.est_level_id=" + estLevelId;
		}

		if (areaId != 0L) {
			tblStr = tblStr + ",area a";
			str = str + " and a.id=e.area_id and (a.id=" + areaId
					+ " or (a.parent_id_str like \'%;" + areaId + ";%\'))";
		}

		if (estManagerId != 0L) {
			str = str + " and e.manager_id=" + estManagerId;
		}

		if (tsId != 0L) {
			tsStr = " and f.tally_set_id=" + tsId;
		} else {
			tsStr = EncCommonHelper.createTallySetSQLStr("f.tally_set_id", ub,
					true);
		}

		str = str + tsStr;
		String sql = "select aa.client_id,est.est_name,aa.year,aa.month,aa.rem_cost from(select f.client_id,year(f.order_date) as year,month(f.order_date) as month,sum(f.cost-be.collected_cost+be.adjust_cost) as rem_cost from "
				+ tblStr
				+ " where f.id=be.id"
				+ " and f.client_id=e.id"
				+ " and be.enabled=\'Y\'"
				+ " and be.examined=\'Y\'"
				+ " and f.order_date>=:yearDate"
				+ str
				+ " group by f.client_id,year(f.order_date),month(f.order_date)"
				+ " union all "
				+ "select f.client_id,0 as year,0 as month,sum(f.cost-be.collected_cost+be.adjust_cost) as rem_cost"
				+ " from "
				+ tblStr
				+ " where f.id=be.id"
				+ " and f.client_id=e.id"
				+ " and be.enabled=\'Y\'"
				+ " and be.examined=\'Y\'"
				+ " and f.order_date<:yearDate"
				+ str
				+ " group by f.client_id"
				+ ") aa,establishment est"
				+ " where aa.client_id=est.id"
				+ " and (aa.client_id in "
				+ "("
				+ "select client_id from ("
				+ "select client_id,sum(f.cost-be.collected_cost+be.adjust_cost) as cost from business_bill f,business_bill_ext be where f.id=be.id and be.enabled=\'Y\' and be.examined=\'Y\' "
				+ tsStr
				+ " group by f.client_id"
				+ ") bb where cost!=0"
				+ ")"
				+ ")";
		Session sess = null;

		List arg20;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			q.setDate("yearDate", yearDate);
			if (estName != null && !estName.equals("")) {
				q.setString("estName", "%" + estName + "%");
			}

			arg20 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg20;
	}

	public List getMtBusinessDateCount(Date startDate, Date endDate, long tsId,
			UserBean ub) throws Exception {
		String str = "";
		if (tsId != 0L) {
			str = str + " and b.tally_set_id=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tally_set_id",
							ub, true);
		}

		String sql = "select sum(fav_cost),sum(cost) from business_bill b,business_bill_ext be where b.id=be.id and be.examined=\'Y\' and be.enabled=\'Y\'"
				+ str;
		if (startDate != null) {
			sql = sql + " and b.order_date>=:startDate";
		}

		if (endDate != null) {
			sql = sql + " and b.order_date<:endDate";
		}

		Session sess = null;

		List arg10;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			if (startDate != null) {
				q.setDate("startDate", startDate);
			}

			if (endDate != null) {
				q.setDate("endDate", endDate);
			}

			arg10 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg10;
	}

	public List getMtBusinessDate(Date startDate, Date endDate, long tsId,
			UserBean ub) throws Exception {
		String str = "";
		if (tsId != 0L) {
			str = str + " and b.tally_set_id=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tally_set_id",
							ub, true);
		}

		String sql = "select i.make_type_id,count(i.id),sum(i.cost) from business_bill b,business_bill_ext be,business_bill_item i where b.id=be.id and b.id=i.bill_id and be.examined=\'Y\' and be.enabled=\'Y\'"
				+ str;
		if (startDate != null) {
			sql = sql + " and b.order_date>=:startDate";
		}

		if (endDate != null) {
			sql = sql + " and b.order_date<:endDate";
		}

		sql = sql + " group by i.make_type_id";
		Session sess = null;

		List arg10;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			if (startDate != null) {
				q.setDate("startDate", startDate);
			}

			if (endDate != null) {
				q.setDate("endDate", endDate);
			}

			arg10 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg10;
	}

	public BigDecimal getReceivableCost(long estId, long tsId) {
		String sql = "select sum(f.rem_cost) from finance_detail f where f.est_id="
				+ estId + " and f.finance_type=\'R\'";
		if (tsId != 0L) {
			sql = sql + " and f.tally_set_id=" + tsId;
		}

		Session sess = null;

		BigDecimal arg9;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			List list = q.list();
			if (!list.isEmpty() && list.get(0) != null) {
				arg9 = (BigDecimal) list.get(0);
				return arg9;
			}

			arg9 = new BigDecimal(0);
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg9;
	}

	public List getAppListCount(Date startDate, Date endDate, String estName,
			String lm, String exam, String clos, String deli, String coll,
			long tsId, UserBean ub, long mngId) throws Exception {
		String str = "";
		if (startDate != null) {
			str = str + " and b.orderDate>=:startDate";
		}

		if (endDate != null) {
			str = str + " and b.orderDate<:endDate";
		}

		if (estName != null && !estName.equals("")) {
			str = str + " and (e.estName like :estName)";
		}

		if (lm != null && !lm.equals("")) {
			str = str + " and (b.linkman like :lm)";
		}

		if (exam != null && !exam.equals("")) {
			str = str + " and be.examined=:exam";
		}

		if (clos != null && !clos.equals("")) {
			str = str + " and be.completed=:clos";
		}

		if (deli != null && !deli.equals("")) {
			str = str + " and be.delivered=:deli";
		}

		if (coll != null && !coll.equals("")) {
			str = str + " and be.collected=:coll";
		}

		if (mngId != 0L) {
			str = str + " and e.managerId=" + mngId;
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		String sql = "select count(*),sum(b.cost),sum(b.cost-be.collectedCost+be.adjustCost) from com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be,com.minierp.model.Establishment e where b.id=be.id and b.clientId=e.id"
				+ str + " and be.enabled=\'Y\'";
		Session sess = null;

		List arg18;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			if (startDate != null) {
				q.setDate("startDate", startDate);
			}

			if (endDate != null) {
				q.setDate("endDate", endDate);
			}

			if (estName != null && !estName.equals("")) {
				q.setString("estName", "%" + estName + "%");
			}

			if (lm != null && !lm.equals("")) {
				q.setString("lm", "%" + lm + "%");
			}

			if (exam != null && !exam.equals("")) {
				q.setString("exam", exam);
			}

			if (clos != null && !clos.equals("")) {
				q.setString("clos", clos);
			}

			if (deli != null && !deli.equals("")) {
				q.setString("deli", deli);
			}

			if (coll != null && !coll.equals("")) {
				q.setString("coll", coll);
			}

			arg18 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg18;
	}

	public List getAppList(Date startDate, Date endDate, String estName,
			String lm, String exam, String clos, String deli, String coll,
			long tsId, UserBean ub, long mngId, String orde, int startIndex,
			int num) throws Exception {
		String str = "";
		if (startDate != null) {
			str = str + " and b.orderDate>=:startDate";
		}

		if (endDate != null) {
			str = str + " and b.orderDate<:endDate";
		}

		if (estName != null && !estName.equals("")) {
			str = str + " and (e.estName like :estName)";
		}

		if (lm != null && !lm.equals("")) {
			str = str + " and (b.linkman like :lm)";
		}

		if (exam != null && !exam.equals("")) {
			str = str + " and be.examined=:exam";
		}

		if (clos != null && !clos.equals("")) {
			str = str + " and be.completed=:clos";
		}

		if (deli != null && !deli.equals("")) {
			str = str + " and be.delivered=:deli";
		}

		if (coll != null && !coll.equals("")) {
			str = str + " and be.collected=:coll";
		}

		if (mngId != 0L) {
			str = str + " and e.managerId=" + mngId;
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		String ordeStr = " order by b.orderDate desc,b.billCode desc";
		if (orde != null) {
			if (orde.equals("ORDE_DATE")) {
				ordeStr = " order by b.orderDate desc,b.billCode desc";
			} else if (orde.equals("DELI_DATE")) {
				ordeStr = " order by b.deliverDate desc,b.billCode desc";
			} else if (orde.equals("BUSI_COST")) {
				ordeStr = " order by b.cost desc,b.billCode desc";
			} else if (orde.equals("BILL_CODE")) {
				ordeStr = " order by b.billCode desc";
			} else if (orde.equals("BILL_CONT")) {
				ordeStr = " order by b.content desc,b.billCode desc";
			} else if (orde.equals("ESTA_NAME")) {
				ordeStr = " order by e.nameSpell desc,b.billCode desc";
			}
		}

		String sql = "select b,e.estName,be from com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be,com.minierp.model.Establishment e where b.id=be.id and b.clientId=e.id"
				+ str + " and be.enabled=\'Y\'" + ordeStr;
		Session sess = null;

		List arg22;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			if (startDate != null) {
				q.setDate("startDate", startDate);
			}

			if (endDate != null) {
				q.setDate("endDate", endDate);
			}

			if (estName != null && !estName.equals("")) {
				q.setString("estName", "%" + estName + "%");
			}

			if (lm != null && !lm.equals("")) {
				q.setString("lm", "%" + lm + "%");
			}

			if (exam != null && !exam.equals("")) {
				q.setString("exam", exam);
			}

			if (clos != null && !clos.equals("")) {
				q.setString("clos", clos);
			}

			if (deli != null && !deli.equals("")) {
				q.setString("deli", deli);
			}

			if (coll != null && !coll.equals("")) {
				q.setString("coll", coll);
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg22 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg22;
	}

	public List getAppListForExamCount(String sk, String ct, long tsId,
			UserBean ub, Date allowStart, long mngId) throws Exception {
		String str = this.getBlearSearchHql(sk);
		if (ct != null) {
			if (ct.equals("NOT_ZERO")) {
				str = str + " and b.cost!=0";
			} else if (ct.equals("ZERO")) {
				str = str + " and b.cost=0";
			}
		}

		if (mngId != 0L) {
			str = str + " and e.managerId=" + mngId;
		}

		if (allowStart != null) {
			str = str + " and b.orderDate>=:allowStart";
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		String sql = "select count(*),sum(b.cost) from com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be,com.minierp.model.Establishment e where b.id=be.id and b.clientId=e.id"
				+ str + " and be.examined=\'N\'" + " and be.enabled=\'Y\'";
		Session sess = null;

		List arg13;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			this.setBlearSearchValue(q, sk);
			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			arg13 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg13;
	}

	public List getAppListForExam(String sk, String ct, long tsId, UserBean ub,
			Date allowStart, long mngId, int startIndex, int num)
			throws Exception {
		String str = this.getBlearSearchHql(sk);
		if (ct != null) {
			if (ct.equals("NOT_ZERO")) {
				str = str + " and b.cost!=0";
			} else if (ct.equals("ZERO")) {
				str = str + " and b.cost=0";
			}
		}

		if (mngId != 0L) {
			str = str + " and e.managerId=" + mngId;
		}

		if (allowStart != null) {
			str = str + " and b.orderDate>=:allowStart";
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		String sql = "select b,e.estName from com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be,com.minierp.model.Establishment e where b.id=be.id and b.clientId=e.id"
				+ str
				+ " and be.examined=\'N\'"
				+ " and be.enabled=\'Y\'"
				+ " order by b.orderDate desc,b.billCode desc";
		Session sess = null;

		List arg15;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			this.setBlearSearchValue(q, sk);
			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg15 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg15;
	}

	public List getAppListForUnExamCount(String sk, long tsId, UserBean ub,
			Date allowStart, long mngId) throws Exception {
		String str = this.getBlearSearchHql(sk);
		if (mngId != 0L) {
			str = str + " and e.managerId=" + mngId;
		}

		if (allowStart != null) {
			str = str + " and b.orderDate>=:allowStart";
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		String sql = "select count(*),sum(b.cost) from com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be,com.minierp.model.Establishment e where b.id=be.id and b.clientId=e.id"
				+ str
				+ " and be.examined=\'Y\'"
				+ " and be.enabled=\'Y\'"
				+ " and be.closed=\'N\'"
				+ " and be.maked=\'N\'"
				+ " and be.hasDeliver=\'N\'"
				+ " and be.hasReceive=\'N\'"
				+ " and be.hasAdjust=\'N\'";
		Session sess = null;

		List arg12;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			this.setBlearSearchValue(q, sk);
			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			arg12 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg12;
	}

	public List getAppListForUnExam(String sk, long tsId, UserBean ub,
			Date allowStart, long mngId, int startIndex, int num)
			throws Exception {
		String str = this.getBlearSearchHql(sk);
		if (mngId != 0L) {
			str = str + " and e.managerId=" + mngId;
		}

		if (allowStart != null) {
			str = str + " and b.orderDate>=:allowStart";
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		String sql = "select b,e.estName,be.examinerName,be.examineTime from com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be,com.minierp.model.Establishment e where b.id=be.id and b.clientId=e.id"
				+ str
				+ " and be.examined=\'Y\'"
				+ " and be.enabled=\'Y\'"
				+ " and be.closed=\'N\'"
				+ " and be.maked=\'N\'"
				+ " and be.hasDeliver=\'N\'"
				+ " and be.hasReceive=\'N\'"
				+ " and be.hasAdjust=\'N\'" + " order by be.examineTime desc";
		Session sess = null;

		List arg14;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			this.setBlearSearchValue(q, sk);
			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg14 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg14;
	}

	public int getAppEditListCount(Date startDate, Date endDate,
			String estName, String creaName, long tsId, UserBean ub)
			throws Exception {
		String str = "";
		if (startDate != null) {
			str = str + " and b.updateDate>=:startDate";
		}

		if (endDate != null) {
			str = str + " and b.updateDate<:endDate";
		}

		if (estName != null && !estName.equals("")) {
			str = str + " and (e.estName like :estName)";
		}

		if (creaName != null && !creaName.equals("")) {
			str = str + " and (b.updateName like :creaName)";
		}

		if (tsId != 0L) {
			str = str + " and bb.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("bb.tallySetId", ub,
							true);
		}

		String sql = "select count(*) from com.minierp.model.BusinessUpdateLog b,com.minierp.model.BusinessBill bb,com.minierp.model.Establishment e where b.billId=bb.id and bb.clientId=e.id"
				+ str;
		Session sess = null;

		int arg13;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			if (startDate != null) {
				q.setDate("startDate", startDate);
			}

			if (endDate != null) {
				q.setDate("endDate", endDate);
			}

			if (estName != null && !estName.equals("")) {
				q.setString("estName", "%" + estName + "%");
			}

			if (creaName != null && !creaName.equals("")) {
				q.setString("creaName", "%" + creaName + "%");
			}

			List list = q.list();
			if (list == null || list.isEmpty() || list.get(0) == null) {
				return 0;
			}

			arg13 = Integer.parseInt(list.get(0).toString());
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg13;
	}

	public List getAppEditList(Date startDate, Date endDate, String estName,
			String creaName, long tsId, UserBean ub, int startIndex, int num)
			throws Exception {
		String str = "";
		if (startDate != null) {
			str = str + " and b.updateDate>=:startDate";
		}

		if (endDate != null) {
			str = str + " and b.updateDate<:endDate";
		}

		if (estName != null && !estName.equals("")) {
			str = str + " and (e.estName like :estName)";
		}

		if (creaName != null && !creaName.equals("")) {
			str = str + " and (b.updateName like :creaName)";
		}

		if (tsId != 0L) {
			str = str + " and bb.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("bb.tallySetId", ub,
							true);
		}

		String sql = "select b,e.estName from com.minierp.model.BusinessUpdateLog b,com.minierp.model.BusinessBill bb,com.minierp.model.Establishment e where b.billId=bb.id and bb.clientId=e.id"
				+ str + " order by b.id desc";
		Session sess = null;

		List arg14;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			if (startDate != null) {
				q.setDate("startDate", startDate);
			}

			if (endDate != null) {
				q.setDate("endDate", endDate);
			}

			if (estName != null && !estName.equals("")) {
				q.setString("estName", "%" + estName + "%");
			}

			if (creaName != null && !creaName.equals("")) {
				q.setString("creaName", "%" + creaName + "%");
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg14 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg14;
	}

	public List getAppBusiDateCoun(Date startDate, Date endDate, long tsId,
			UserBean ub, long mngId) throws Exception {
		String sql = "select count(b.id),sum(b.cost) from business_bill b,business_bill_ext be,establishment e where b.id=be.id and b.client_id=e.id and be.examined=\'Y\' and be.enabled=\'Y\'";
		if (startDate != null) {
			sql = sql + " and b.order_date>=:startDate";
		}

		if (endDate != null) {
			sql = sql + " and b.order_date<:endDate";
		}

		if (mngId != 0L) {
			sql = sql + " and e.manager_id=" + mngId;
		}

		if (tsId != 0L) {
			sql = sql + " and b.tally_set_id=" + tsId;
		} else {
			sql = sql
					+ EncCommonHelper.createTallySetSQLStr("b.tally_set_id",
							ub, true);
		}

		Session sess = null;

		List arg11;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			if (startDate != null) {
				q.setDate("startDate", startDate);
			}

			if (endDate != null) {
				q.setDate("endDate", endDate);
			}

			arg11 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg11;
	}

	public List getAppBusiDateCounImpr(Date startDate, Date endDate, long tsId,
			UserBean ub, long mngId) throws Exception {
		String sql = "select count(b.id),sum(b.imprest) from business_bill b,business_bill_ext be,establishment e where b.id=be.id and b.client_id=e.id and be.examined=\'Y\' and be.enabled=\'Y\' and b.imprest!=0";
		if (startDate != null) {
			sql = sql + " and b.order_date>=:startDate";
		}

		if (endDate != null) {
			sql = sql + " and b.order_date<:endDate";
		}

		if (mngId != 0L) {
			sql = sql + " and e.manager_id=" + mngId;
		}

		if (tsId != 0L) {
			sql = sql + " and b.tally_set_id=" + tsId;
		} else {
			sql = sql
					+ EncCommonHelper.createTallySetSQLStr("b.tally_set_id",
							ub, true);
		}

		Session sess = null;

		List arg11;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			if (startDate != null) {
				q.setDate("startDate", startDate);
			}

			if (endDate != null) {
				q.setDate("endDate", endDate);
			}

			arg11 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg11;
	}

	public List getAppEstBusiCounTota(Date startDate, Date endDate,
			String estName, long tsId, UserBean ub, long mngId)
			throws Exception {
		String str = "";
		if (startDate != null) {
			str = str + " and b.order_date>=:startDate";
		}

		if (endDate != null) {
			str = str + " and b.order_date<:endDate";
		}

		if (estName != null && !estName.equals("")) {
			str = str + " and (e.est_name like :estName)";
		}

		if (mngId != 0L) {
			str = str + " and e.manager_id=" + mngId;
		}

		if (tsId != 0L) {
			str = str + " and b.tally_set_id=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tally_set_id",
							ub, true);
		}

		String sql = "select count(*),sum(cost),sum(cost+adjust_cost-collected_cost) as rem_cost from ( select sum(b.cost) as cost,sum(be.adjust_cost) as adjust_cost,sum(be.collected_cost) as collected_cost from business_bill b,business_bill_ext be,establishment e where b.id=be.id and b.client_id=e.id and be.enabled=\'Y\' and be.examined=\'Y\'"
				+ str + " group by e.id" + ") aa";
		Session sess = null;

		List arg13;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			if (startDate != null) {
				q.setDate("startDate", startDate);
			}

			if (endDate != null) {
				q.setDate("endDate", endDate);
			}

			if (estName != null && !estName.equals("")) {
				q.setString("estName", "%" + estName + "%");
			}

			arg13 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg13;
	}

	public List getAppEstBusiCoun(Date startDate, Date endDate, String estName,
			long tsId, UserBean ub, long mngId, int startIndex, int num)
			throws Exception {
		String str = "";
		if (startDate != null) {
			str = str + " and b.order_date>=:startDate";
		}

		if (endDate != null) {
			str = str + " and b.order_date<:endDate";
		}

		if (estName != null && !estName.equals("")) {
			str = str + " and (e.est_name like :estName)";
		}

		if (mngId != 0L) {
			str = str + " and e.manager_id=" + mngId;
		}

		if (tsId != 0L) {
			str = str + " and b.tally_set_id=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tally_set_id",
							ub, true);
		}

		String sql = "select est_name,cost,adjust_cost,collected_cost,cost+adjust_cost-collected_cost as rem_cost,manager_name,area_id,id from ( select e.est_name,sum(b.cost) as cost,sum(be.adjust_cost) as adjust_cost,sum(be.collected_cost) as collected_cost,e.manager_name,e.area_id,e.id from business_bill b,business_bill_ext be,establishment e where b.id=be.id and b.client_id=e.id and be.enabled=\'Y\' and be.examined=\'Y\'"
				+ str + " group by e.id" + ") aa" + " order by aa.cost desc";
		Session sess = null;

		List arg15;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			if (startDate != null) {
				q.setDate("startDate", startDate);
			}

			if (endDate != null) {
				q.setDate("endDate", endDate);
			}

			if (estName != null && !estName.equals("")) {
				q.setString("estName", "%" + estName + "%");
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg15 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg15;
	}

	public List getAppEstUnRece(String estName, String ct, String rt,
			long tsId, UserBean ub, long mngId, Date allowStart,
			int startIndex, int num) throws Exception {
		String str = "";
		if (estName != null && !estName.equals("")) {
			str = " and (e.est_name like :estName)";
		}

		if (mngId != 0L) {
			str = str + " and e.manager_id=" + mngId;
		}

		if (allowStart != null) {
			str = str + " and f.bill_date>=:allowStart";
		}

		if (tsId != 0L) {
			str = str + " and f.tally_set_id=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("f.tally_set_id",
							ub, true);
		}

		String creCostStr = "";
		if (ct != null && !ct.equals("")) {
			if (ct.equals("Y")) {
				creCostStr = " and rem_cost>=credit_cost";
			} else if (ct.equals("N")) {
				creCostStr = " and rem_cost<credit_cost";
			}
		}

		String costStr = " rem_cost!=0";
		if (rt.equals("ZERO")) {
			costStr = " rem_cost=0";
		}

		String sql = "select a.* from(select e.est_name,sum(f.rem_cost) as rem_cost,e.credit_cost,e.bus_time,e.mark,e.manager_name,e.area_id,e.id from finance_detail f,establishment e where f.est_id=e.id and f.finance_type=\'R\'"
				+ str
				+ " group by e.id"
				+ ") a"
				+ " where "
				+ costStr
				+ creCostStr + " order by rem_cost desc";
		Session sess = null;

		List arg18;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			if (estName != null && !estName.equals("")) {
				q.setString("estName", "%" + estName + "%");
			}

			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg18 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg18;
	}

	public List getAppEstUnReceTota(String estName, String ct, String rt,
			long tsId, UserBean ub, long mngId, Date allowStart)
			throws Exception {
		String str = "";
		if (estName != null && !estName.equals("")) {
			str = " and (e.est_name like :estName)";
		}

		if (mngId != 0L) {
			str = str + " and e.manager_id=" + mngId;
		}

		if (allowStart != null) {
			str = str + " and f.bill_date>=:allowStart";
		}

		if (tsId != 0L) {
			str = str + " and f.tally_set_id=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("f.tally_set_id",
							ub, true);
		}

		String creCostStr = "";
		if (ct != null && !ct.equals("")) {
			if (ct.equals("Y")) {
				creCostStr = " and rem_cost>=credit_cost";
			} else if (ct.equals("N")) {
				creCostStr = " and rem_cost<credit_cost";
			}
		}

		String costStr = " rem_cost!=0";
		if (rt.equals("ZERO")) {
			costStr = " rem_cost=0";
		}

		String sql = " select count(*),sum(rem_cost) as rem_cost from( select sum(f.rem_cost) as rem_cost,e.credit_cost from finance_detail f,establishment e where f.est_id=e.id and f.finance_type=\'R\'"
				+ str
				+ " group by e.id"
				+ ") a"
				+ " where "
				+ costStr
				+ creCostStr;
		Session sess = null;

		List arg16;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			if (estName != null && !estName.equals("")) {
				q.setString("estName", "%" + estName + "%");
			}

			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			arg16 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg16;
	}

	public List<?> getListForSetu(String searchKey, String type,
			long estManagerId, long tsId, UserBean ub, Date allowStart,
			int startIndex, int num) throws Exception {
		String str = "";
		if (searchKey != null) {
			searchKey = searchKey.trim();
		}

		if (searchKey != null && !searchKey.equals("")) {
			str = " and ((b.bill_code like :searchKey)  or (e.est_name like :searchKey) or (e.name_spell like :searchKey) or (b.content like :searchKey) or (b.phone like :searchKey))";
		}

		if (tsId != 0L) {
			str = str + " and b.tally_set_id=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tally_set_id",
							ub, true);
		}

		if (type != null && type.equals("COMP")) {
			str = str + " and be.completed=\'Y\'";
		}

		if (estManagerId != 0L) {
			str = str + " and e.manager_id=" + estManagerId;
		}

		if (allowStart != null) {
			str = str + " and b.order_date>=:allowStart";
		}

		String sql = "select bb.id,bb.bill_code,bb.order_date,ee.est_name,bb.content,bb.deliver_date,bb.setup_require,bbe.has_setu from business_bill bb inner join business_bill_ext bbe on bb.id=bbe.id inner join establishment ee on bb.client_id=ee.id inner join ( select distinct b.id from business_bill_item bi inner join business_bill b on bi.bill_id=b.id inner join business_bill_ext be on b.id=be.id inner join establishment e on b.client_id=e.id where be.closed=\'N\' and be.examined=\'Y\' and be.enabled=\'Y\' and be.setuped!=\'Y\' and bi.is_setu=\'Y\'"
				+ str
				+ ") aa on bb.id=aa.id"
				+ " order by bb.id desc,bb.bill_code desc";
		Session sess = null;

		List arg15;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			if (searchKey != null && !searchKey.equals("")) {
				q.setString("searchKey", "%" + searchKey + "%");
			}

			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg15 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg15;
	}

	public int getListForSetuCount(String searchKey, String type,
			long estManagerId, long tsId, UserBean ub, Date allowStart)
			throws Exception {
		String str = "";
		if (searchKey != null) {
			searchKey = searchKey.trim();
		}

		if (searchKey != null && !searchKey.equals("")) {
			str = " and ((b.bill_code like :searchKey)  or (e.est_name like :searchKey) or (e.name_spell like :searchKey) or (b.content like :searchKey) or (b.phone like :searchKey))";
		}

		if (tsId != 0L) {
			str = str + " and b.tally_set_id=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tally_set_id",
							ub, true);
		}

		if (type != null && type.equals("COMP")) {
			str = str + " and be.completed=\'Y\'";
		}

		if (estManagerId != 0L) {
			str = str + " and e.manager_id=" + estManagerId;
		}

		if (allowStart != null) {
			str = str + " and b.order_date>=:allowStart";
		}

		String sql = "select count(*) from ( select distinct b.id from business_bill_item bi inner join business_bill b on bi.bill_id=b.id inner join business_bill_ext be on b.id=be.id inner join establishment e on b.client_id=e.id where be.closed=\'N\' and be.examined=\'Y\' and be.enabled=\'Y\' and be.setuped!=\'Y\' and bi.is_setu=\'Y\'"
				+ str + ") aa";
		Session sess = null;

		int arg14;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			if (searchKey != null && !searchKey.equals("")) {
				q.setString("searchKey", "%" + searchKey + "%");
			}

			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			List list = q.list();
			if (list.isEmpty()) {
				return 0;
			}

			arg14 = Integer.parseInt(list.get(0).toString());
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg14;
	}

	public int getListForCooperCount(Date orderStart, Date orderEnd,
			Date deliStart, Date deliEnd, String estName, String billCode,
			String follName, long tsId, UserBean ub, long mngId)
			throws Exception {
		String str = this.getListForCooperSql(orderStart, orderEnd, deliStart,
				deliEnd, estName, billCode, follName, tsId, ub, mngId);
		String sql = "select count(*) from ( select b.id from Business_bill_item i inner join business_bill_item_ext ie on i.id=ie.id inner join business_bill b on b.id=i.bill_id inner join business_bill_ext be on b.id=be.id inner join establishment e on b.client_id=e.id left join cooper_bill_item ci on ci.bb_item_id=i.id where be.enabled=\'Y\' and be.examined=\'Y\' and be.closed=\'N\' and ie.coop_comp=\'N\' and i.is_cooper=\'Y\'"
				+ str + " group by b.id" + ") aa";
		Session sess = null;

		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			this.setListForCooperPara(q, orderStart, orderEnd, deliStart,
					deliEnd, estName, billCode, follName);
			List list = q.list();
			if (!list.isEmpty()) {
				int arg18 = Integer.parseInt(list.get(0).toString());
				return arg18;
			}
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return 0;
	}

	public List<?> getListForCooper(Date orderStart, Date orderEnd,
			Date deliStart, Date deliEnd, String estName, String billCode,
			String follName, long tsId, UserBean ub, long mngId,
			String orderBy, String ascOrDesc, int startIndex, int num)
			throws Exception {
		String str = this.getListForCooperSql(orderStart, orderEnd, deliStart,
				deliEnd, estName, billCode, follName, tsId, ub, mngId);
		String orderStr = "";
		orderStr = " order by bb.deliver_date desc,bb.bill_code desc";
		if (orderBy != null && !orderBy.equals("")) {
			if (orderBy.equals("ORDER_DATE")) {
				orderStr = " order by bb.order_date";
			} else if (orderBy.equals("DELIVER_DATE")) {
				orderStr = " order by bb.deliver_date";
			} else if (orderBy.equals("BILL_CODE")) {
				orderStr = " order by bb.bill_code";
			} else {
				orderStr = " order by bb.deliver_date";
			}

			orderStr = orderStr + " " + ascOrDesc;
		}

		String sql = "select bb.id,bb.bill_code,bb.order_date,ee.est_name,bb.linkman,bb.content,bb.deliver_date,bb.follower_name_str,aa.coop_num from business_bill bb inner join establishment ee on bb.client_id=ee.id inner join ( select b.id,count(ci.id) as coop_num from Business_bill_item i inner join business_bill_item_ext ie on i.id=ie.id inner join business_bill b on b.id=i.bill_id inner join business_bill_ext be on b.id=be.id inner join establishment e on b.client_id=e.id left join cooper_bill_item ci on ci.bb_item_id=i.id where be.enabled=\'Y\' and be.examined=\'Y\' and be.closed=\'N\' and ie.coop_comp=\'N\' and i.is_cooper=\'Y\'"
				+ str + " group by b.id" + ") aa on bb.id=aa.id" + orderStr;
		Session sess = null;

		List arg22;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			this.setListForCooperPara(q, orderStart, orderEnd, deliStart,
					deliEnd, estName, billCode, follName);
			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg22 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg22;
	}

	private String getListForCooperSql(Date orderStart, Date orderEnd,
			Date deliStart, Date deliEnd, String estName, String billCode,
			String follName, long tsId, UserBean ub, long mngId) {
		String str = "";
		if (orderStart != null) {
			str = str + " and b.order_date>=:orderStart";
		}

		if (orderEnd != null) {
			str = str + " and b.order_date<:orderEnd";
		}

		if (deliStart != null) {
			str = str + " and b.deliver_date>=:deliStart";
		}

		if (deliEnd != null) {
			str = str + " and b.deliver_date<:deliEnd";
		}

		if (estName != null && !estName.equals("")) {
			str = str + " and (e.est_name like :estName)";
		}

		if (billCode != null && !billCode.equals("")) {
			str = str + " and (b.bill_code like :billCode)";
		}

		if (follName != null && !follName.equals("")) {
			str = str + " and (b.follower_name_str like :follName)";
		}

		if (mngId != 0L) {
			str = str + " and e.manager_id=" + mngId;
		}

		if (tsId != 0L) {
			str = str + " and b.tally_set_id=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tally_set_id",
							ub, true);
		}

		return str;
	}

	private void setListForCooperPara(SQLQuery q, Date orderStart,
			Date orderEnd, Date deliStart, Date deliEnd, String estName,
			String billCode, String follName) {
		if (orderStart != null) {
			q.setDate("orderStart", orderStart);
		}

		if (orderEnd != null) {
			q.setDate("orderEnd", orderEnd);
		}

		if (deliStart != null) {
			q.setDate("deliStart", deliStart);
		}

		if (deliEnd != null) {
			q.setDate("deliEnd", deliEnd);
		}

		if (estName != null && !estName.equals("")) {
			q.setString("estName", "%" + estName + "%");
		}

		if (billCode != null && !billCode.equals("")) {
			q.setString("billCode", "%" + billCode + "%");
		}

		if (follName != null && !follName.equals("")) {
			q.setString("follName", "%" + follName + "%");
		}

	}

	public List getBusiList(long estId, Date startDate, Date endDate,
			int startIndex, int num) throws Exception {
		String str = "";
		if (startDate != null) {
			str = str + " and bb.order_date>=:startDate";
		}

		if (endDate != null) {
			str = str + " and bb.order_date<:endDate";
		}

		String sql = "select bb.id,bb.bill_code,bb.order_date,bb.deliver_date,bi.make_type_id,bi.finished_content,bi.pro_name,bi.specification,bi.unit,bi.amount,bi.square_num,bi.unit_price,bi.cost,bi.make_require,bi.price_type,bi.leng_type,be.examined,bi.wc_id,bi.is_cooper,bie.wc_comp,bie.coop_comp,bi.amount,bie.deli_amou,be.closed,bb.address,bb.mark,bb.linkman,bb.phone from business_bill_item bi left join business_bill_item_ext bie on bi.id=bie.id left join business_bill bb on bb.id=bi.bill_id left join business_bill_ext be on bb.id=be.id where be.enabled=\'Y\' and bb.client_id="
				+ estId + str + " order by bb.id desc,bi.id";
		Session sess = null;

		List arg11;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			if (startDate != null) {
				q.setDate("startDate", startDate);
			}

			if (endDate != null) {
				q.setDate("endDate", endDate);
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg11 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg11;
	}

	public List getBusiListCount(long estId, Date startDate, Date endDate)
			throws Exception {
		String str = "";
		if (startDate != null) {
			str = str + " and bb.order_date>=:startDate";
		}

		if (endDate != null) {
			str = str + " and bb.order_date<:endDate";
		}

		String sql = "select count(*),sum(bi.cost) from business_bill_item bi left join business_bill bb on bb.id=bi.bill_id left join business_bill_ext be on bb.id=be.id where be.enabled=\'Y\' and bb.client_id="
				+ estId + str;
		Session sess = null;

		List arg9;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			if (startDate != null) {
				q.setDate("startDate", startDate);
			}

			if (endDate != null) {
				q.setDate("endDate", endDate);
			}

			arg9 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg9;
	}

	public List getBusiCoun(long estId) throws Exception {
		String sql = "select sum(f.con_cost) as con_cost,sum(f.adj_cost) as adj_cost,sum(f.rp_cost) as rp_cost,sum(f.rem_cost) as rem_cost from finance_detail f where f.est_id="
				+ estId + " and f.finance_type=\'R\'";
		Session sess = null;

		List arg6;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			arg6 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg6;
	}

	public List getPayList(long estId, Date startDate, Date endDate,
			int startIndex, int num) throws Exception {
		String busiStr = "select b.order_date as bill_date,b.content as mark,b.bank_account_id as ba_id,b.imprest as cost from business_bill b,business_bill_ext be where b.id=be.id and b.client_id="
				+ estId
				+ " and b.imprest!=0"
				+ " and be.enabled=\'Y\'"
				+ " and be.examined=\'Y\'";
		String receStr = "select b.receive_date as bill_date,b.description as mark,b.bank_account_id as ba_id,b.cost from receive_bill b where b.client_id="
				+ estId + " and b.enabled=\'Y\'" + " and b.examined=\'Y\'";
		if (startDate != null) {
			busiStr = busiStr + " and b.order_date>=:startDate";
			receStr = receStr + " and b.receive_date>=:startDate";
		}

		if (endDate != null) {
			busiStr = busiStr + " and b.order_date<:endDate";
			receStr = receStr + " and b.receive_date<:endDate";
		}

		String sql = "select * from(" + busiStr + " union all " + receStr
				+ ") a" + " order by bill_date desc";
		Session sess = null;

		List arg12;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			if (startDate != null) {
				q.setDate("startDate", startDate);
			}

			if (endDate != null) {
				q.setDate("endDate", endDate);
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg12 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg12;
	}

	public List getPayListCount(long estId, Date startDate, Date endDate)
			throws Exception {
		String busiStr = "select b.order_date as bill_date,b.content as mark,b.bank_account_id as ba_id,b.imprest as cost from business_bill b,business_bill_ext be where b.id=be.id and b.client_id="
				+ estId
				+ " and b.imprest!=0"
				+ " and be.enabled=\'Y\'"
				+ " and be.examined=\'Y\'";
		String receStr = "select b.receive_date as bill_date,b.description as mark,b.bank_account_id as ba_id,b.cost from receive_bill b where b.client_id="
				+ estId + " and b.enabled=\'Y\'" + " and b.examined=\'Y\'";
		if (startDate != null) {
			busiStr = busiStr + " and b.order_date>=:startDate";
			receStr = receStr + " and b.receive_date>=:startDate";
		}

		if (endDate != null) {
			busiStr = busiStr + " and b.order_date<:endDate";
			receStr = receStr + " and b.receive_date<:endDate";
		}

		String sql = "select count(*),sum(cost) from(" + busiStr
				+ " union all " + receStr + ") a";
		Session sess = null;

		List arg10;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			if (startDate != null) {
				q.setDate("startDate", startDate);
			}

			if (endDate != null) {
				q.setDate("endDate", endDate);
			}

			arg10 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg10;
	}
}